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.