SSIS ForEach loop container

Posted: 14 June 2010 in Uncategorized
Tags: ,

In this article I will describe how the For Each loop container works and how to make path references variable.

We have the following situation:

  • A source directory with flat files which we want to import. (source1.txt, source2.txt, source3.txt)
  • A destination directory to import the data. (destination.txt)

Source1.txt contains:

John;Doe;41
Jane;Smith;23
Peter;Pan;16

Source2.txt contains:

Jill;Valentine;23
Alex;Anderson;32

Source3.txt contains:

Mark;Vandenberg;54
Samantha;Smith;28

Destination.txt is empty.

We want to load each of the source files and import the data into the destination file. To do so I will use a For Each loop container.

Let’s get started with creating a new Visual Studio 2008 SSIS project.

First we drag and drop the For Each loop container into the package.

In this container we will create the Data Flow Task to process the source files into the destination file. Lets Drag & Drop the Data Flow Task into the container and give it a descriptive name.

Now we go into the Data Flow Task and create the task as if we want to load a single file into the destination. This is done easily. We Create a Flat File Source and a Flat File Destination.

Double click the Flat File Source and configure the connection to go to source1.txt. To do so click on “New…” in the Flat File Source Editor. There you can configure the filename to load, the Connection manager name and the delimiters.

With this configured we can get a preview of our data by clicking on Columns or Preview. If everything is OK you will see 3 records. Close the screens by clicking OK.

Now we want to configure the Flat File Destination. Double click on the Flat File Destination. And create a new File Connection in the File Connection Manager. Point the File name to the destination.txt file. And uncheck the option to overwrite the data in the destination file.

Click on Mappings. Visual studio maps the source and destination columns automatically.

Close the screen and you will see that the red mark in the source and destination are gone.

We have a working Data Flow Task which imports one single source file into our destination file. Now we have to configure this Data Flow Task to load every file in the source directory. To do so we need to configure the For Each loop container.

Lets start this by creating a package variable for storing the current source file name. Go to the Control Flow and right click in the workspace area. In the menu select Variables. It is important to click on an empty space otherwise the variable will be in the wrong variable scope. Create the string variable CurrentFile.

Value  can be empty because we will load the value dynamically from within the For Each loop container. Right click on the For Each loop container and select Edit. In the Collection options set the folder to the directory of the source files. And set the Files hash to *.txt to load every text file.

In the Variable Mappings select User::CurrentFile. This is our Variable we created in the steps above. By mapping this variable to the For Each loop container we will set the Variable value with every file the collection loads.

With this steps done we have finished configuring the For Each loop container. Now we need to go back to our Data Flow Task and configure the Flat File Source to use the CurrentFile variable. To do so we will edit the connection string of the flat file connection in the connection manager. The connection manager is located at the bottom of the screen.

Right Click on the SourceFile connection and select properties. To the right in the properties window clear the connection string value.

After cleaning the connection string value select Expressions and click on the “…”-Button at the end.

Select the ConnectionString property and use the Expression: “@[User::CurrentFile]”. By doing so you map the user Variable which the For Each loop container sets to the file connection.

Now we can run the SSIS package and check if the For Each loop container loops trough all the source files. After running we open the destination.txt file to verify that the data is loaded into the destination.

Advertisements
Comments
  1. First post reserved

  2. MakingUsmile says:

    I used to be suggested this blog by means of my
    cousin. I aam not positive whether this publish is written by means of him as no one else
    know such targeted apporoximately my trouble.
    Yoou are incredible! Thank you!

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