CODEWITHSUNDEEP
sqlsql-serverssis
olf
olf

Reputation: 872

SSIS Script Component - only to change variables

I have a series of task that are very similar:

SELECT a,b FROM c
Lookup in another table and change value in column b. Save new value back to c and if not match, send the result on to an error table.

That part is pretty straight forward and illustrated here:

Source ==> Lookup =match=> SQL Update command
           =No match=> SQL Save Error command

(Hope you understand what I mean - but it works!)

I now have to repeat this a number of times, where my source-sql changes. So what I want to do is to insert a Script Component in front of the Source and set my User::Sql variable like:

Variables.Sql = "SELECT d, e FROM f"

All of the above is contained in a Data Flow. When I have created one I can then copy that one and only change the Sql variable in the script and then it should all work.

My problem is: When I insert the Script Command it asks me if it is a Source, Destination or Transscript script. And by only setting the variable it does not produce any rows for output and cannot connect to my Source.

Anyone know how to make that work?

(I have simplified the above. I actually want to update multiple variables and use those in my Source, Lookup and Error update as well - therefore it is not more simple just to change the SQL script in the initial Source! But being able to do the above, I will be able to achieve what I want :-))

Upvotes: 3

Views: 176

Answers (1)

Ndech
Ndech

Reputation: 965

You should set your variable containing the SQL query in the control flow, before you execute the dataflow.

Then you need to use that variable as an expression in your Dataflow. You can parametrize the query used in the lookup or any other parameters of your dataflow.

If your dataflows really have always the same structure, you could even generate a list of queries and call your dataflow task in a loop, preventing the duplication of the same tasks.

Upvotes: 4

Related Questions