Looping Through Rows in a Table
Put your left foot in, then your left foot out... Yeah, writing loops to iterate over rows in a table is kinda like that.
- By Eric Johnson
- 11/02/2009
Inevitably when writing code, you'll 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 as 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 (see Fig. 1).
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.
 |
| Figure 1. Creating the Data Flow for the loop in our example. |
Step two is creating the Foreach Loop Container (see Fig. 2). Add the container to your package and make sure it's situated 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.
 |
| Figure 2. Creating the Foreach Loop Container via this dialog. (Click image to view larger version.) |
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.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.