There are varieties of looping statement in the programming language such as (For, while, do, for (object)). SSIS does have looping task such as For Loop and Foreach Loop. I have written an article about for loop in this site. But, today I am going to explain about Foreach loop and who we can make use of it in the ETL packages.
This container holds a set of tasks and executes it as the total count of the objects in the looping variable. We can call this set of values as enumerator.
Below given is the set of enumerators list that can be used with Foreach loop container
We might have already seen lot of article in internet that talks about file enumerator. But, in this article I am going to talk about “Foreach Item enumerator”. It is very simple and easy to use when you have a set of fixed values that should be passed on in the iteration.
Scenario
I have a requirement to upload 5 flat files from different locations. For the simplicity all files are same in structure and the target is SQL Server Database. In this example I will show you how to configure Foreach loop with my 5 file path using Foreach Item enumerator.
Implementation
Step 1: Open BIDS and Choose SSIS project.
Step 2: create a package and add Foreach loop container in to it.
Step 3: edit the container and go to collections tab
Step 4: Select Foreach List Enumerator from the Enumerator drop down list.
At this point, we have to feed the values for the enumerator.
- Create a column with the string data type. To do this, click the columns button below the empty list box.
- Now, Add a column by click the add button in the newly appeared dialogue box. Press ok.
- Finally, provide necessary values in the column
Step 5: Click variable mapping tab and configure a variable which will hold the looping value in to it while run time
Step 6: Click OK and execute the package.
In this example, I have shown how to setup Foreach Loop container with Foreach List Enumerator. To enhance this package, you can just add a dataflow and create a file connect and map the variable to the file connection.
Thanks for reading. If you need any assistance just write a mail to me.
