Geeks With Blogs
SharePoint & SQL Thoughts
I recently had a client request to create an SSIS package that would loop through a set of data in SQL tables to allow them to complete their data transformation processes. Knowing that Integration Services does have ForEachLoop Container, I knew the task would be easy but the moment I jumped into it I figured there was no straight forward way to accomplish the task since for each didn’t really have a loop through the table enumerator. With the capabilities of integration Services, I was still confident that it was possible it was just a matter of creativity to get it done.
I set out to discover what different ForEach Loop Editor Enumerators did and settled with Variable Enumerator.  Here is how I accomplished the task.
1.       Drop your ForEach Loop Container in your WorkArea.
2.       Create a few SSIS Variable that will contain the data.

Fig1

Notice I have assigned MyID_ID variable a value of “TEST’ which is not evaluated either. This variable will be assigned data from the database hence allowing us to loop.
3.       In the ForEach Loop Editor’s Collection select Variable Enumerator

Fig2

4.       Once this is all set, we need a mechanism to grab the data from the SQL Table and assigning it to the variable.

Fig3

Fig: Select Top 1 record

Fig4

Fig: Assign Top 1 record to the variable
5.       Now all that’s required is a house cleaning process that will update the table that you are looping so that you can be able to grab the next record
 
A look of the complete package

Fig5

 

Posted on Friday, May 7, 2010 3:04 AM | Back to top


Comments on this post: SSIS ForEachLoop Container

Comments are closed.
Comments have been closed on this topic.
Copyright © Leonard Mwangi | Powered by: GeeksWithBlogs.net