We help IT Professionals succeed at work.

Package variable in SSIS

Im having trouble getting a package variable from control flow over to data flow.

All I need is the date that the package is run and that to become a part of the sql where clause in the data flow task.

Just need someone to help me get from point a to b.  The control flow gets a rundate from a control table.  Then attempts to hand that off to the data flow which uses that rundate as part of the where clause.  Currently im getting parameter errors.  It doesnt seem to like the variable/parm being passed in.

See attachments. control flow dataflow parm:rundate qry
Comment
Watch Question

Author

Commented:
When I try to preview the query I keep getting the following error.

No value given for one or more required parameters.   preview error

Commented:
In the first Execute SQL Task, you need to go to the ResultSet option in the Task. In there you will specify 0 and the Variable you want to store the value in.

Then in the Data Flow task, where you have the ?, then click on Parameters button and choose your variable under the Variable option.

Once you have that, the variable should be available to you.  I can do screenshots if you need them.

Author

Commented:
Yes sir screen shots would be great if its not too much to ask.  If I can get this step down it would be great.

Author

Commented:
Do you mean this option? resultset type
Commented:
OK, here goes.  To start off with, this is the way I have done it.  You can map it to your solution I hope.

I created a Variable to store the value I am going to use in the query.  Click on View - Other Windows - Variables.  In the Variables Window you will see the button in the upper left corner illustrated in the first graphic.  Click that and make sure that you click in the package window first, so that the scope of the variable is Package.  You name the variable and give it a type and even an initial value if you want.

Next, I add the Execute SQL task and configure it with a query.  In my case I just did SELECT 10 to give me the value of 10 which I will use in the query afterward.  So I have set up my connection and stuff, but the other thing is on the ResultSet tab, I have indicated that I want the Result Set Name to be 0 and put in Var1, which is my variable.  The key here is that if you are returning a result set of 1 row, you can see that I indicated "Single Row" in my result set on the first page. And if I had multiple things returned, like SELECT 10, 20 then I would add a Result Set Name of 1 and so forth.

Once that is set, when the Execute SQL task executes, you will have values in the Variables.

This feeds into the Data Flow Task, and you add a Source and Destination.  I configure the Source to have a Query in a SQL Command, and put the PlanID > ? which is how you indicate a parameterized query.  

I then click on the Parameters button and configure the parameters with Parameter0 to be User::Var1.

When I execute the package, you see in the last graphic that I now have the values > 10 which is in my variable.


SSIS-Variables.jpg
SSIS-ExecuteSQL.jpg
SSIS-ExecuteSQL-Editor.jpg
SSIS-ResultSet.jpg
SSIS-OLEDB-Editor.jpg
SSIS-Parameters-Query.jpg
SSIS-Output-gt-10.jpg

Author

Commented:
Awsome with a captial A  thanks!