July 7, 2011 at 1:53 pm
I am trying to pass a variable from 1 sql task into other to extract some data from source. running into problems when setting the varibles as Input and Output for both sql tasks
Has anyone tried this before?any links or any exampels where similar process has been implemented will be of great help. TIA
July 7, 2011 at 2:10 pm
Have done this many times.
What part is causing you trouble? The passing of the value INTO the next step or getting the value OUT of the previous step?
CEWII
July 8, 2011 at 6:36 am
Thanks for the response . infact i am having trouble storing the value into a variable as i dont see it when i debug the task. so prettty much both storing in a variable and using that variable for another task. any suggesti0ons will be of great help. tIA
July 8, 2011 at 6:47 am
So am I right in thinking that you are attempting to use an Execute SQL task to retrieve a single figure and then to store that figure in a variable for use later in the package?
Can you confirm the data type and scope of the variable?
Perhaps you could attach a screen shot showing how the Execute SQL task is configured, showing the SQL Source and the properties - that will help us diagnose what is going wrong.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2011 at 8:25 am
attached is the document with snapshots. yes i have 2 sql execute tasks 1st one passes a variable to 2nd one which uses that variable to pull data from source connection
July 8, 2011 at 8:32 am
I think you need to change this code inside the script task:
from tablea a, tableb b
where a.familyid = b.familyid
and b.groupid = [user::GroupID]
to this:
from tablea a, tableb b
where a.familyid = b.familyid
and b.groupid = ?
SSIS uses ? to represent passed in parameters, the order they are used in the sql code needs to be the same as the order they defined within the parameter mapping screen of the task
July 8, 2011 at 9:58 am
Tried substituting the variable with ? instead of User::GroupID.Still not able to get it work. Am i missing anything in initial setting? any suggestions please?? TIA
July 8, 2011 at 10:04 am
Does the code work if you type in the value rather than trying to pass in a parameter?
July 8, 2011 at 10:20 am
Yes it does. I followed this link for setting the variables. Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply