SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow

OK, let’s continue from the last blog SSIS – “Full result set” to a set variable of Object.

We have read a list of dates from a SQL query into a set variable vLoopSet.

Now we are ready to pass vLoopSet into a Foreach Loop Container.

Step 1: Create a Foreach Loop Container

In the Collection tab, pick Foreach ADO Enumerator as Enumerator. There are many choices for the Enumerator. I have not worked with types other than Foreach ADO Enumerator.

Then in the Enumerator configuration section, pick User::vLoopSet as the ADO object source variable, and rows in all the tables as Enumeration mode.

In the Variable Mappings tab, add a mapping by setting User::vLoopDate as Variable, and 0 (zero) as Index. Index 0 (zero) indicates that we want to use the first column from the result set.

vLoopDate is a previously created variable.

In the next blog, I’ll continue to show how to pass the date variable into a OLE DB Source in a Data Flow.