SSIS 2008: Looping Through Rows in a Table

Inevitably when writing code, you have a need to write a loop in order to iterate over multiple objects. When writing code against a SQL Server, as we usually are doing in SSIS Packages, you often need to iterate over all the rows in a table. This can be done using an SSIS Foreach Loop Container, but the how is not obvious. Making this work is a two step process. You have to create a data flow to populate an SSIS variable with your table data, and then you have to configure your loop.

First, let’s take a look at getting your table of information into an SSIS variable. You will need to add a new variable to the package with a Data Type of Object. This variable will hold a recordset that will represent your table. Next, you need to create a Data Flow, like the one shown here.

The OLE DB Source will pull the data from your table or query and the recordset destination is used populate you variable. At this point the data from your table is stored in an ADO Recordset in your variable.

Step two will be creating the Foreach Loop Container, shown below. Add the container to you package and make sure it is after the data flow you used to populate your variable. On the Collection page of the Foreach Loop Container’s properties, select Foreach ADO Enumerator as your enumerator and set your variable as the ADO Object Source. Next, make sure you choose Rows in the First Table under enumeration mode. Finally, you can move on to the Variable Mapping page and define where the values in each column are stored. This is done with a 0 based index where 0 is your first column, 1 is second column and so on. Define a variable for each column you need to read and each time the loop is executed the variables will be updated.

That’s it, you can put any code you want in the Loop Container to execute it for each row in a table.