Dyanmic Value Variables In SSIS
May 29, 2007
in my package, I have serveral data flow sources using a same date value. the date value is different everytime the package is executed. (e.g. the date is two weeks before the execution date) I want to create something in my package which can dynamically calculate the datetime and pass it to all the data flow sources.
I thought variable is a good choice. but i don't know how to make the value dynamic.
anyone please help.
thanks
View 9 Replies
ADVERTISEMENT
Sep 12, 2007
(sorry, I don't know how to make the copied tables 'look right' but spaces should --> tabs)
Branches Table
B_ID Branch
0 OLD
1 ADM
2 CL1
3 CL2
4 NEB
5 PTO
6 HOL
9 MKT
10 CL3
12 IOW
15 IND
16 PAP
18 SOU
19 NOR
Value Table (Pk = Vid, FK = Bid)
VID BID Emp_id Value
1 4 1 100
2 4 1 200
3 10 1 300
4 3 2 400
5 4 2 500
6 4 2 600
Desired Result View
(Pivot on On Emp_id sum the value column)
Emp_ID OLD ADM CL1 CL2 NEB PTO HOL MKT CL3 IOW IND PAP SOU NOR
1 0 0 0 0 300 0 0 0 0 0 300 0 0 0
2 0 0 0 400 1100 0 0 0 0 0 0 0 0 0
My current solution is to use a pivot query similar to the below (note: ..etc. used to simplify the discussion), It gets me close with the exception of presenting the null values as null rather than 0, I can make that work!
SELECT Emp_id,[0] AS OLD, [1] AS AD, [2] AS CL1, [3] AS CL2, [4] AS NEB ....etc)
FROM (SELECT Emp_id, value, B_id
FROM Branches) P PIVOT (sum(value) FOR B_idId IN ([0], [1], [2], [3], [4].."etc.")) AS pvt
"Take the data from the Branches table, sum up the values for a employee then present as a "pivoted" sql view with each branch in a column and nulls converted to 0"
Finally here is the problem/question.
I don't want to 'hard' code the branches (as the solution above does), they will change over time as branches are added and I want the query to reflect these changes. The problem, I don't know how to proceed from here, I have looked at using a SSAS solution, but that seems like too much for such a simple problem and in any case, I don't know how to integrate the SSAS cube into the current Ms Access 2000 solution (moving from Access backend data store to SQL server 2005).
Note: all the examples I have found in the BOL and forums, hard code the columns
I am frankly overwhelmed be the number of options available in SQL Server 2005, and still am trying to 'get my mind' around some of the concepts. I'm sure I don't know many of the options available, much more how to best implement them. The BOL, while good, have not really provided the enlightment I seek.
If I have to go to a CLR solution, could one provide some simple guidance,
Thanks.
Ed Warren.
View 8 Replies
View Related
May 16, 2006
Hi there,
This is what I am trying to do :
1) Create a global variable.
2) assign a value to the variable using an SQL Task. (For this all I want to do is assign a maximum value from a column in my table)
3) use the variable in a Data flow task (using SQL to get my source data)
I know this must be very simple but I am new to SSIS and I am struggling to achieve this task.
Thanks for your help in advance.
View 11 Replies
View Related
Aug 2, 2007
Hi,
I'm trying to use a variable in a OLEDB Command, but i can't do it.
What i want to do is:
Step 1: Count the number of rows of a table (using count rows transofrmation) and save it to a variable 'var1'.
Step2: Insert some rows on this table.
Step 3: Count again the number of rows and save it on another variable 'var2'.
Step 4: Ina OLEDB Command , get the inserted records in step 2 to manipulate them on other tables...
Something like that: select top (@var2 - @var1) * from table order by desc
¿Can i use local or global variables from an OLEDB Command transformation object?
View 3 Replies
View Related
Sep 17, 2015
I have a process which connects to a SQL server remotely, runs some code, creates a temporary table with an output. I want to be able to select the results of this table into XML (not a problem), put the results into a variable in SSIS, and put the variable results into a SQL table a different SQL Instance. Doing it this way removes the footprint of needing a normal table on the source SQL Server.
View 2 Replies
View Related
Jul 10, 2007
Before in DTS, I used to using ActiveX scripts to setup simple Global Variables that would later be used by the package, let's say a give state or a date.
I'm completely lost how to do the same in SSIS, can someone give me a hand?
I'm not sure what component to use and how to read this variable.
View 2 Replies
View Related
Apr 27, 2007
Hello,
I am wondering what is the difference between assigning variables in the following two ways.1.
Dim myVar11 As Variables
Dts.VariableDispenser.LockOneForWrite("LoopCount", myVar11)
myVar11("LoopCount").Value = 32.
Dts.Variables("LoopCount").Value = 3I received this error when processing SSIS Package.
Error: A deadlock was detected while trying to lock variables
"User::LoopCount" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Your help is appreciated,
-Lawrence
View 7 Replies
View Related
Aug 15, 2006
I have a SSIS package that was migrated from DTS 2000. It had a variable that I passed to the DTS package when executing. Now when I try executing the SSIS package with the variable, I get the following error: The package path referenced an object that cannot be found: "package.variables[user::SnapShotDate].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
From everything that I can find, I am passing the variable correctly. If I look at the package within BIDS, my variable is listed under the variable window & it is scoped for the package. Any ideas on what I might be missing here? This is my first SSIS package that contains a variable.
Let me know if you need more info.
Thanks!
John
View 3 Replies
View Related
Jul 7, 2007
I have a simple task that I would like to manage through a SSIS package but do not know how to accomplish it. I need to perform the following tasks:
1. update a sql server table (ecwcust) and set the ftpstatus column to 'P' when the column's value is 'E'.
update ecwcust set ftpstatus = 'P' where ftpstatus = 'E'
2. declare a variable and set the variable to the number of rows that have a ftpstatus equal to 'P'
declare @newcustomercount int
select @newcustomercount = count(*) from ecwcust where ftpstatus = 'P'
3. if @newcustomercount > 0, copy all of the rows from ecwcust where ftpstatus = 'P' to an excel spreadsheet.
4. send an email to an email recipient with the excel spreadsheet as an attachment.
5. delete the excel spreadsheet.
I do not know how to create the variable @newcustomercount so that I can determine whether or not to create the excel spreadsheet and copy the records to it.
I have read all the online help and stuff that MS has but I can't understand the instructions. Please point me to some instructions that are understandable.
View 3 Replies
View Related
Oct 22, 2007
Hi there,
I have a script which grabs data from a source server. The problem is I have over 50 source servers, so I have to make a script for each one.. EXACTLY the same code but different linked server address.
So I was wondering if i can put the server name into a variable so I only need to make one script.
Is that possible?
thanks,
View 3 Replies
View Related
Jun 6, 2006
Greetings my SQL friends,
I have a very simple package which consists of a SQL Task component and a Script Task.
The SQL Task retrieves a single value from 1 row table in one of my database and assigns that value to a variable. The Script task simply outputs the value of the variable in to a message box. I know, it's all simple stuff (I am trying to master SSIS )
The problem I have is that when I change the value of the column in my table, the value of the variable is not changing when I re-run my package. It seems to be the same value every time.
What am I doing wrong?!
Your help would be much appreciated.
View 8 Replies
View Related
Jul 24, 2007
Hi all of you,
I don't think that such thing is possible but either way, I ask, just in case...
We're doing lots of packages which must have the same variables (in terms of datatype, name, etc..)
TIA for that,
View 5 Replies
View Related
Feb 25, 2006
I need to pass a value to a package level variable in an SSIS package from a SQL Agent job. If I use dtexecui the syntax for passing the variable in the Property Path is
Package.Variables[User::MyVar].Properties[Value]
and the package runs fine.
When I use this syntax, or any similar format I can think of, in SQL Agent, my job fails. In SQL Agent you put this in the Property Path on the Set Values tab of the Step that runs the package. If I remove passing in the variable my package runs fine.
BOL says use the format Package<container name>.<property name> but I can find no examples. Can anyone provide a working sample to make this work?
Thanks.
View 4 Replies
View Related
Jan 22, 2008
I have defined an active X task (converting from SQL 2000)
Function Main()
DTSGLOBALVARIABLES("TEST").Value= InputBox("TEST : ","Message Box")
Main = DTSTaskExecResult_Success
End Function
The problem is that this variable is not recognized by the subsequent tasks although I have defined a variable "TEST" in the variable lists.
Is there something else to do to get it recognized ?
Thanks for helping,
Pierre
View 8 Replies
View Related
Dec 2, 2014
I have this procedure to remove certain characters from file names.
The SQL Task has this: exec dbo.spCleanseFileName @strFileName = ?, @strFileNameCleansed = ?
The stored procedure:
CREATE PROCEDURE [dbo].[spCleanseFileName](@strFileName varchar(40),@strFileNameCleansed varchar(40) output)
I have it in an SSIS package and my problem is that, after that SQL Task completes, the value for the
),@strFileNameCleansed variable is blank. I HAVE confirmed that the procedure DOES set the correct value inside the SP.
View 2 Replies
View Related
Jan 15, 2015
I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.
I used a select from a central table to get the connection strings, which is put into [User::Server2]
1) execute SQL task to collect the port
(DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
)
Which is then put into a variable @Portnumber
2) I then need to insert this into a certain server with a where clause including another variable
so something like
"INSERT INTO DBO.InstanceConfig VALUES ('"+@[User::Portnumber]+"') where Serverinstance = ('"+@[User::Server2]+"')
but it doesnt work like that.. is there an easier way
View 1 Replies
View Related
Feb 6, 2008
How do i access global variables in SSIS scripts ?
the Dts.variables("VarName").Value
should work but doesn't ?
do i need to reference the variable another way ?
View 1 Replies
View Related
Nov 30, 2007
I'm trying to understand how to use SSIS variables within SQL commands and which Data Flow toolbox objects I can use for this. My simple package has a OLE DB Source object that returns the rows in a table. For the next operation, the output from the OLE DB Source is input into a Lookup data flow transformation where there is a select query that returns another value from a different table...
select bla from myTable where dbname = db_name()
The output is then passed on to an OLE DB Destination.
The above lookup query only works because, it so happens, that I could get a unique and correct result back filtered by current database name. However, due to other required changes, I now have introduce more 'where clauses'. I have all the necessary clause information in SSIS variables (iterated via a For Each loop). I would like do something like...
select bla from myTable where dbname = ? and bla_type = ?
However, there isn't a facility for Parameters in the Lookup object's 'use results of a SQL query'. I can see that I can use parameters in the OLE DB Source object by changing the 'data access mode' to SQL Query. However, it doesn't seem valid to join two OLE DB Source objects together in the data flow? I can't see any other suitable objects in the Data Flow toolbox. Ideally, I would be able to use Parameters in the Lookup object or some other object that would do the same job.
Thanks,
Clive
View 6 Replies
View Related
Jan 14, 2008
Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net
Regards
Ravi
View 1 Replies
View Related
Nov 21, 2006
Hi
I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?
Please let me know if theres a way to access SSIS variables.
Thanks,
Vipul
View 10 Replies
View Related
May 13, 2008
This is a fun one. I have a job that runs a SSIS package. It has some variables that I set a runtime. How can I programmatically change the values for these variables using SMO? I can get to the step in the job using VB but I can't find a way to programmatically change the value of the parameters of the job. The code loops through every property of the SSIS step. There's a command string but that's an ugly beast to code against. Any help is appreciated.
With oStep
Console.Write("Name: " + .Name + vbTab)
Console.Write("Proxy Account: " + .ProxyName + vbTab)
Console.Write("Type: " + .SubSystem.ToString + vbTab)
Console.WriteLine("Urn to point to job: " + .Urn.ToString)
iProp = .Properties.GetEnumerator
While iProp.MoveNext
Console.WriteLine("Property Name/Value: " + Convert.ToString(iProp.Current))
End While
End With
View 3 Replies
View Related
Dec 13, 2007
I'm not sure if this is the right forum, but i'm having an issue where SSIS Variables window will not come up. The most i get is a gray bar that looks like it is supposed to hold the variables.
I've tried getting it to show by:
1) Menu --> SSIS --> Variables
2) Right Click --> Variables
3) Menu --> View --> Other Windows --> Variables
The same thing seems to happen for "log events" in Menu --> View --> Other Windows --> Log Events.
Does anyone know how to fix this? Is anyone else having this problem?!?!?!
-Thanks Thames
View 6 Replies
View Related
Sep 25, 2007
Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure
are the variables i am populating Mapping them to result set in execute sql task.
This is the criteria for script task to Fail or success
I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.
I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF
View 36 Replies
View Related
Oct 2, 2006
hello all,
I am having a hard time referenceing system variables. Can some one give me a quick lesson?
I am trying to save system variables suck as StartTime finishtime and processes ran?
I want to store these items into a table for use later.
View 10 Replies
View Related
Feb 8, 2006
I have a bunch of variables which I need to initialize from Parameters table in SQL Server database.
I could think of the following two ways
1) Have one SQL Task for each variable and assign a query such as - Select ParamValue from Parameters where ParamChar = '<KeyName>'
2) Have a script task and write VB code to connect to the database and fire one query for every variable and set it accordingly.
I am not totally convinced with both of these approaches. Is there any better approach than these...
Thanks,
Loonysan
View 1 Replies
View Related
Dec 15, 2006
I'm trying this code in a script task inside a foreach loop that gets a file in a foreach file enumerator and maps it to a variable FileName.
In the script task I'm setting the ReadOnlyVariables to @[User::FileName]
: Imports System.IO Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main() Dim fname As String = CType(Dts.Variables("FileName").Value, String) File.Move(fname, fname + ".processed") Dts.TaskResult = Dts.Results.Success End Sub
End Class
But now getting this error:
Error: Failed to lock variable "\xxxxDataf1.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
As you can see the variable does appear to be resolving.
View 1 Replies
View Related
Jul 3, 2014
I have finally been able to get access to SQL Server 2012, so I am just now getting to play with some of the features. My new company has multiple ways of calling an SSIS package, and some (if not all) use Package Parameters. I just did a little research on these, and I in general get how they work. Here is why I ask...
I liked how my last company had dynamic variables setup. They had all of the required variables stored on a single database table on a single server. This kept all of the connections strings, file paths, file names, etc. that a package would use. My computer, the UAT server, and the Prod server all had the same file in the same location with 1 character difference to note which environment to run in. This way I knew I would always use only the Dev server when I ran a package during development. I would also only touch the test folders with my test data. Once we moved it to UAT or Prod, that single character difference would point to a different set of variables for the same PackageID, and we didn't have to worry about forgetting to change hard coded variable values. The only 2 hard coded variable values were the PackageID and the variable holder database. I have heard of similar ways of doing this via an XML file, but I have not had the (ahem) pleasure of working with that yet.
Jump to my current job and why so far I have not been impressed with the Package Parameters. We have 3 different jobs that all call the same SSIS package. We feed in a different FacilityID, and using values stored in tables we know to look for different file names, etc. To me this seems to be a similar set up to how my last company had set the system. The big difference is my old company used a Script Task to verify which environment we were running in (I can see setting this for different FacilityID's, but that would be cumbersome), and my new company uses multiple tables across multiple databases (I know, this is not the best set up in the world, and we are fixing it soon) to decide what we will use for some of the variable values.
What are your thoughts on storing variables outside a package? Do you have any blog posts or articles you can link to show current best practices?do the Package Parameters only work via the Integrated Services Catalog, or can they be accessed for the File System packages too?
View 2 Replies
View Related
Nov 18, 2014
I am using Variables to configure a lot in an SSIS Packages.
Over the years you add new variables that are usefull to your default-package.
However to use those in "older" packages you have to open add them manually.
Any way (e.g. skript) to add a set of variables to every SSIS-package in a folder ?
View 3 Replies
View Related
Sep 9, 2015
Is it possible to export the environment variables for an SSIS 2012 project? And if so how is it achieved?
View 2 Replies
View Related
Mar 12, 2008
Hi all,
I use a "Exec DTS 2000" task.
I have a variable user::MYVAR in my SSIS package, wich value is "PARENT".
I pass this variable as an outer variable to my dts 2000 package, wich receive it correctly, and then update it to "UPDATED".
After this task finishes, when I come back to SSIS, MYVAR is still at "PARENT".
Is it possible to see the update from the parent package ?
Thanks
View 6 Replies
View Related
Jun 1, 2006
Hi
Does anybody know how to pass values from asp dot net to SSIS package variables ?
Currently I have an SSIS package for monitoring windows service... for that...
I have to pass the Server-IP Addrress, UserName, Password, Service Name as Parameter.
I would like to pass these parameters through an Interface from RUN TIME.
Please help this problem
Regards
Deepu M.I
View 1 Replies
View Related
May 8, 2008
I have created two User variables at Package level (OldRunID{Int32} and NewRunID{Int32}). I am using two simple ExecuteSQL Tasks to get a Single Row Resultset return (each) and loading them (on Result Set Page). Using the Constraint and Expression Flow controls, I have found that they load to True (-1) instead of the 25 and 50, respectively, that I would expect. I then wrote a Script task to evaluate their values and find that I get zero for Dts.Variables.Count. - Why am I not loading the variables correctly and why can I not access them in the Script task?
View 18 Replies
View Related
Jan 14, 2008
Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net
Regards
Ravi
View 1 Replies
View Related
