SSIS Script Component - only for changing variables
I have a number of tasks that are very similar:
SELECT a, b FROM c
Search another table and change the value in column b. Store the new value with c and if it doesn't match, send the result to the error table.
This part is pretty straight forward and is illustrated here:
Source ==> Lookup =match=> SQL Update command
=No match=> SQL Save Error command
(I hope you understand what I mean, but it works!)
Now I have to repeat this several times when the source code changes. So I want to add a Script component in front of the source and set the User :: Sql variable like:
Variables.Sql = "SELECT d, e FROM f"
All of the above is contained in the data stream. When I created it, I can then copy it and change the Sql variable in Script and then it all works.
My problem is, when I paste in a Script command, it asks me if it is a source, destination, or Transscript script. And just by setting the variable, it doesn't create strings for output and can't connect to my source.
Does anyone know how to make this work?
(I've simplified this. I really want to update a few variables and use them in my update of Source, Lookup and Error, so it's not easier to just change the SQL Script in the original source! But being able to do this, I should be able to achieve what I want: - ))
source to share
You must set the variable containing the SQL query in the control flow before executing the data flow.
Then you need to use this variable as an expression in your data stream. You can parameterize the query used in search or any other data flow parameters.
If your data streams do have the same structure, you can even generate a list of requests and call the data flow task in a loop, preventing duplication of the same tasks.
source to share