July 8, 2010 at 4:25 am
hi
i have 2 tasks in my ssis. first i try to identify my 'filename' which can be changed any moment.
then i want to pass this variable to a SQL task editor. i defined the mapping parametres (i've added sfilename , varchar...etc) .. and
i try to use the variable in a direct query,
like ;
insert into t_temptable_filename
select @sfilename
but i get the error that 'you have to define @sfilename...'.
may be it not possible to make it?
any ideas ?
thanks in advance.
July 8, 2010 at 9:08 am
What you need to do is define a variable with a Global scope (or within the scope of the container that both tasks are in). Then in the Execute SQL Task, set the variable in the "Result Set" window. Then, in the Script Task, pass the variable in the ReadVariables or ReadWrite Variables (if you intend to reset it) of the script task.
You can't define the variables using standard T-SQL variables like @MyVar. When reading in variables into an Execute SQL Task, you have to use Parameter mapping to set the variables in order (0-n) then use ? when executing your SQL.
To explain the T-SQL task better: I have a variable called FileName listed in the package variables which either has the value hard coded, uses an expression, or gets its value from the config file. Then, in the T-SQL task -> Parameter Mapping, I hit Add and add User::FileName with a direction of Input, the datatype of the variable (string in this case) and a Parameter Name of 0. In the General tab, my code would look like:
Insert into MyTable (FileName)
Select ?
Make sense?
July 21, 2010 at 8:28 am
thakns really much. It is a really good explication.
July 21, 2010 at 8:46 am
Glad I could help. Let me know if you have any other questions on this subject.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply