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.

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.

Data Flow for the loop
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.

Foreach Loop Container
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.

Featured

  • Skytap on Azure Service Adds Options for Apps Running on IBM Systems

    Applications that use IBM Power processors in "on-premises" datacenters can now be moved to Microsoft Azure datacenters via a "Skytap on Azure" service, according to Thursday announcements by both Microsoft and Skytap.

  • Microsoft Didn't Remove the SMB1 Protocol from Windows

    Microsoft explained in a Wednesday announcement that it didn't actually remove Server Message Block 1 (SMB1) from Windows releases.

  • Exchange Online Users Get More Caveats on Basic Authentication's End in October

    Microsoft on Tuesday offered more details on its plans to end Basic Authentication in Exchange Online, which will cause pain for some organizations.

  • How To Install the Windows 10X Emulator

    Earlier this month, Microsoft released a public preview of Windows 10X, a spinoff of the Windows 10 operating system that's designed to run on the forthcoming multiscreen Surface devices. Here's how to take it out for a spin.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.