June 10, 2011 at 4:59 am
[font="Verdana"]Hi All,
I have one SSIS package in which there is one 'Execute SQL Task' and one 'Data Flow Task'. I have declared one variable in 'Execute SQL Task' and tried to use it into 'Data Flow Task' however I am not able to use it. I even tried to use global veriable as well, however could not succeded. Can you please let me know, how to use such variable throughout the package scope?
Thanks in advance,
-- Mahesh
[/font]
MH-09-AM-8694
June 10, 2011 at 5:40 am
It sounds as if you have defined your variable at the DFT scope rather than the package. Not all "Global" variables are at the package scope either.
Explain more what it is you are trying to achieve as there could be something more specific going wrong.
June 10, 2011 at 6:13 am
There's a difference between package variables and the variables declared within an Execute SQL Task. Could you post details on exactly how you set up and populated this variable? It will help us help you better.
June 10, 2011 at 6:31 am
[font="Verdana"]
Below is the flow of the SSIS package.
1 'Execute SQL Task'
...
Declare@UserTypeIDBigInt
Select@UserTypeID = UserTypeID From dbo.User(NoLock) Where UserType = 'Customer'
Insert Into <temp Table>
Select<Columns>
From<tables>
WhereUserTypeID = @UserTypeID
...
2. 'Another Execute SQL Task'
Select<Columns>
From<anothertables>
Where... And UserTypeID = @UserTypeID
3. 'Final Data Flow Task'
Here I am refering the variable @UserTypeID declared in 1st 'Execute SQL Task' which is not allowing me to use in 'Final Data Flow Task'. So can anybody explain how to use such variables?
Hopes I have explained the stuff clearly. Please do let me know incase other information needed.
Thanks again,
[/font]
MH-09-AM-8694
June 10, 2011 at 6:36 am
Mahesh Bote (6/10/2011)
[font="Verdana"]Below is the flow of the SSIS package.
1 'Execute SQL Task'
...
Declare@UserTypeIDBigInt
Select@UserTypeID = UserTypeID From dbo.User(NoLock) Where UserType = 'Customer'
Insert Into <temp Table>
Select<Columns>
From<tables>
WhereUserTypeID = @UserTypeID
...
And in what part of this statement are you populating the package variable? I don't see the value moving outside of this code to the result set.
June 10, 2011 at 6:52 am
[font="Verdana"]Brandie, currently I have just declared the SQL variables, not SSIS Package variables. And the variables I have decalred in the first 'Execute SQL Task', wanted to use in other Tasks as well. So can you please let me know is it possible to declare the variables in one task and can be fetch or used in the next tasks? Or please let me know how to over come the situation.
thanks,
--Mahesh[/font]
MH-09-AM-8694
June 10, 2011 at 6:54 am
Yes in your execute SQL task you must map the result of your SQL variable to a variable in the package.
In fact I don't know why you would need the DECLARE etc. in your SQL at all.
You could just have:
SELECT UserTypeID From dbo.User(NoLock) Where UserType = 'Customer'
and then map UserTypeID from your result to the relevant variable.
For the second Execute SQL task, if you want to use the variable then you will have to set up another string variable that captures the SQL using your existing variable.
So set the properties of the new variable as "Evaluuate as Expression" to True and then in the expression type your query, like so (with quotations):
"Select <Columns>
From <anothertables>
Where ... And UserTypeID = "+[User::Your Variable Name]
Then change the input method of your execute SQL to be from a variable and choose your SQL variable.
Hope this helps 🙂
June 10, 2011 at 7:00 am
Ahha. Now I understand the problem.
In the package white space, right click once and choose Package Variables. It will bring up a new window (at the side or on the bottom) where you can create the variable. I won't go into details on scope and types (you should read up on that in Books Online). But you create one and name it without the '@' sign and I believe you want INT64 for a bigint datatype in your Execute SQL Task.
Add the following code to your first task (at the end): Select @UserTypeID.
In the General tab of the task, change Result Set from None to Single Row.
Click on Result Set in the left hand side. Click Add. Change Result Name to 0 (zero) and VariableName to your new variable.
Then, in your later task, you will add the package variable to the Data Flow. Since I don't know where you are adding it or how you are using it, I can't advise on that. But if you read up on variables in BOL and make sure you're reading on SSIS variables, you should get the information you need.
June 10, 2011 at 7:01 am
yayomayn (6/10/2011)
Yes in your execute SQL task you must map the result of your SQL variable to a variable in the package.In fact I don't know why you would need the DECLARE etc. in your SQL at all.
You could just have:
SELECT UserTypeID From dbo.User(NoLock) Where UserType = 'Customer'
No, not really. If you read his code for that section, he's doing a table insert based on the variable. So the variable usage there makes sense.
June 10, 2011 at 7:07 am
Brandie Tarvin (6/10/2011)
yayomayn (6/10/2011)
Yes in your execute SQL task you must map the result of your SQL variable to a variable in the package.In fact I don't know why you would need the DECLARE etc. in your SQL at all.
You could just have:
SELECT UserTypeID From dbo.User(NoLock) Where UserType = 'Customer'No, not really. If you read his code for that section, he's doing a table insert based on the variable. So the variable usage there makes sense.
Doh! Missed that! :doze:
June 10, 2011 at 7:38 am
[font="Verdana"]Thank you all!!![/font]
MH-09-AM-8694
June 10, 2011 at 8:22 am
Glad we could help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply