Home > SSIS > Foreach Loop Container

Foreach Loop Container


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.

  1. Create a column with the string data type. To do this, click the columns button below the empty list box.
  2. Now, Add a column by click the add button in the newly appeared dialogue box. Press ok.
  3. 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.

MSDN Link

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: