THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

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.

clip_image002[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.

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.

clip_image004[1]

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

Published Friday, November 13, 2009 4:09 PM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Taffy Lewis said:

This example is hard to follow, I'm don't understand about the desination recordset

January 5, 2010 10:15 AM
 

dan said:

good example which fills the gap in sql server documentation on this.

January 28, 2010 11:53 AM
 

Mitch said:

Thanks! This was confounding me. Great example.

August 16, 2010 5:02 PM
 

M&S said:

Thanks!Thanks!Thanks!Thanks!Thanks!Thanks!

September 29, 2010 9:22 AM
 

Sam said:

November 23, 2010 6:43 AM
 

Andy said:

Thank you so much!

April 28, 2011 11:42 AM
 

John said:

Thanks! Very informative.

June 9, 2011 12:54 PM
 

Ryan said:

Thanks for the article but a little difficult to follow along with for those unfamiliar with ssis

July 28, 2011 10:42 AM
 

mark said:

great, thank you!

August 12, 2011 3:00 AM
 

New comer said:

Thank You

August 16, 2011 7:59 PM
 

Minnie.Schurr said:

I found it is very good example as it does not get you lost in the too detailed or too specific case. This way people see the big picture and they can go ahead to learn the detailed task if needed (for example record set destination).

Thanks

Min

August 23, 2011 7:36 PM
 

2nd New Comer said:

Boooo!

September 20, 2011 2:26 PM
 

deepakm said:

Very useful. Got it to work in less than a minute. Thanks a lot.

November 8, 2011 6:11 AM
 

Hiren Desai said:

Better is what u perform at database side

December 2, 2011 4:43 AM
 

DM said:

This is exactly what I needed, worked perfectly!  Thank you!

January 26, 2012 6:07 PM
 

safsdfdsdfsfdssdfdsfsdds said:

sdfds

January 28, 2012 2:42 AM
 

narasimha said:

it is easy to understand and very useful.Thanx a lot.

April 24, 2012 7:35 AM
 

Eleysha said:

Very helpful, thanks! The ForEachLoop image was kind of fuzzy, but otherwise, perfect. :)

July 16, 2012 2:39 PM
 

Dave said:

Dude I can't even see your image for the for loop.  It's too tiny!  please update with a normal size print screen.

September 13, 2012 3:48 PM
 

LRHG said:

Thanks! Very helpful!

January 15, 2013 6:41 AM
 

Rajkukmar said:

thanks.. very helpful.

April 21, 2013 4:35 AM
 

Kevin B said:

Thanks, works perfectly!

June 3, 2013 3:49 PM
 

zaf said:

I am having an issue with ado for loop

if you can help can you email me sullyf50@hotmail.com

July 18, 2013 2:42 PM
 

Ashish Garg said:

People, refer to this article for more clarity:

http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html

July 22, 2013 1:41 AM
 

Isaac said:

This example was very hard for me to follow.  It was also missing the steps for setting up the Recordset Destination task, which shows errors and there was no information for how to complete this ??

August 13, 2014 3:29 PM
 

Andy Russell said:

Great explanation, thanks.

August 25, 2014 6:44 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement