How to Looping through Result Sets in SSIS For Foreach Loop Container

Problem: When you would like to loop through result sets stored in an object variable, you probably will use Foreach Loop Container in SSIS. If you need to read multiple fields from object variables to use in the down stream later, how to implement it?

Solution: 

To specify the mapping by indexes in "Variable Mapping" of Foreach Loop (FEL) container.

Here we use a dynamic parent-child loading pattern as an example. For details about dynamic parent-child loading pattern, please refer to the book "SQL Server 2012 Integration Services Design Patterns".  Basically, this design pattern uses a control table to control which tables to load and which child packages to call at this execution. Later in the Foreach Loop, we can use Execute Package task to call related child packages. This dynamic parent-child design pattern is especially helpful when you have lots of tables to refresh while refreshing frequencies are different. So the parent package will be designed as shown below. It consists of two main tasks: one Execute SQL task to extract child packages to call for current execution; the other is Foreach Loop container to loop through result sets from above Execute SQL task, also use Execute Package Task to call child package at each loop.

We extract two columns from a control table by SQL scripts and loaded the results into an Object variable called User::PkgList. 

SELECT TableName, [ChildPackageName]
FROM [dbo].[PackageListWithOrder]
where IsLoad = 1
order by LoadOrder 

As shown below, User::PkgList contains table names and related package names.

Step 1: Define the ADO enumerator as usual in Foreach Loop Container.

Step 2: Go to "Variable Mappings", then specify the variables to be mapped with,following the order in your Object variables with index starts from 0. Note that you can't find the "Add" button to add additional mapping. It doesn't matter. You just click the space after first row, then you can specify the new mapping.

Then you can use these two variables in Execute Package Task to call and pass through related child packages. Run the package to see the variables' values got updated when looping through Foreach loop! Let me know if you have any questions.