Variable's Default value is not changing in SSIS while using Data Flow Task and Execute SQL Task?

0
votes

I have created 4 variables in my SSIS package.

Condition String >=
Value  String 2010
FilterCLM String Year
Table Test
SQL String

SQL Variable gives Dynamic SQL query as below.

"select * from " +  @[User::Table] + " where " +  @[User::FilterColumn] + " " + @[User::Condition] + " " +  @[User::Value]

I am creating Sequence Container in SSIS package. First thing I am doing is getting the value from the SQL table which store the values for the Variable using Execute SQL task. Second thing I am trying to do is Loading using Data Flow Task and choose SQL Command from Variable, giving variable name but it is not giving me updated value which is read from table. It is giving me default value which I assigned.

How can I modify this package in order to make it work ?

1
sqltsqlssisssis-2012

1 Answers

0
votes

Can you give some more information like will your query in SQL Execute task returns more then one row ?

If it returns only one row make sure your Result Set = Single row and assign appropriate Result Name to the variables in ResultSet.

If it returns more then one row the Result Set = Full Result Set and store the returned values in object and then read values from object to variables before using it in SQL Command

Thanks, Santhosh