SSIS – Pass a list of values to a data flow source
I wrote a blog post SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow a few days ago.
But when do we really need the Foreach Loop Container? I find that I do not use it very often. The reason is that I can only pass one at a time the value of the variable to the data source of the data flow inside the Foreach Loop Container. I don’t have a benchmark number of how much overhead that adds to my run time, but if I have 100 values to pass, I start to feel uneasy.
I have a list of status IDs I maintain on the SQL server side. Status IDs is one of the dimensions for my fact table. I want to limit the number of records I bring down from our DB2 data warehouse using the list of the Status IDs.
1) Can I maintain the status ID dimension table on DB2? Yes, I can. But I prefer not to do that because by default all our development is supposed to be on SQL server.
2) Can I use MERGE in the data flow, merging my source from DB2 with this Status ID table on SQL Server? Not really. My query on the DB2 side uses several temp tables due to the complexity the query. Without physically staging those temp data, I cannot really use MERGE.
3) Can I create a package variable to hold a dynamic SQL query with a parameter? No. The Status IDs I need to use is not just one value, but a list of many values.
4) How about hard coding those Status IDs in my DB2 query. No. Thanks. No hard coding in this case.
Here is the solution I came up with, and so far it is working perfectly for me.
Step 1: create a SQL table to hold those Status IDs on the SQL Server side
Step 2: in SSIS package, create an Execute SQL Task to read in the Status ID values, and concatenate all the rows into a comma-delimited string, and store the string as a single value into a user defined variable
Step 3: create a user defined function on DB2 to convert the list of the Status IDs into rows. (see my next blog post on how I did that)
Step 4: create a data flow with a parameterized data source, and pass the user defined variable into the data source as the parameter.
More to come on this…