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.

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

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

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