Load multiple files with SSIS Foreach Loop Container

You just finished your SSIS project where you needed to load a CSV file into a Sql Server database table, but now you just found out that multiple files with the same data will now need to be loaded.  Using the Foreach Loop Container in SSIS makes this relatively easy.

Issue:

Below you see our current task, it has a control flow and double clicking on it shows a data flow that reads from a flat file source and puts the data into a database table.

 

mf1

mf2

 

Here is a sample of the data that gets loaded.  Our current Flat File Connection is hard coded to the File1.txt file.

 

mf4

 

Running the package goes without any issues.

mf3

Checking the destination, shows that the data got loaded successfully.

mf5

 

 

Solution:

Now, we have multiple files that need to be loaded.

On the control flow screen, drag the Foreach Loop Container onto the control flow canvas like below.

mf6

The difference between the For Loop and the Foreach Loop container can be thought of as the following:  The For Loop runs a specified number of times that you provide the component.  If you set it up to run 4 times, it will always run 4 times.

The Foreach Loop runs any number of times depending on the criteria of your loop.  Our Foreach Loop will run as many times as needed to load any amount of files using our Data Flow task.  If there is 1 file, it will loop 1 time, if there are 50 files, it will loop 50 times, automatically.

Click the links below to read more about them.

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/for-loop-container

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container

 

Now drag our current data flow task into the Foreach Loop Container, by just dragging and dropping.

mf7

 

Now we need to setup a variable.  You do that be clicking the highlighted yellow item below.  That pops up a box that allows you to create a variable.  I am naming ours: Filefullpath, and choosing String as the data type.

mf8

 

After the variable is created, double click the Foreach Loop container to configure it.  Click the Collection tab on the left.  Starting at the top, for our example choose Foreach File Enumerator.  For the folder, pick the location for the files that will need to be loaded.  We are only going to be loading text files, so chose that as the extension.  Finally, select Fully Qualified, as we need that to populate our variable with the full directory and name information.

mf10

 

On the Variable Mapping tab on the left, we need to provide our variable name that we created before.  Click the down arrow and choose: User::Filefullpath, and then put a zero in the index field.

mf11

 

Now we need to configure our Flat File Connection Manager on the data flow tab to use our variable instead of the current hard coded filename that is currently setup.  Right click the flat file connection manager on the bottom of the screen and choose Properties.

 

mf12

 

On the Properties screen, scroll down until you see Expressions and click to create an expression.

mf13

 

That brings up the Property Expression Editor.  Under property, select ConnectionString as that is the field we need to modify and then click the expression to create a new expression.

mf14

 

That brings up the Expression Builder.  Sometimes this screen can be confusing, but for this project it is easy.  On the left side under Variables and Parameters, look for the variable we created called User::Filefullpath and just drag it down to the expression box below.  Press Ok twice.

mf15

 

Believe it or not, we are done configuring the Foreach Loop.  Let us try running the package and see what happens.  Success!!!

mf20

 

Looking at our database table, it looks like all of the data got loaded.

mf16

 

The files that got loaded.

mf17

 

Below is the progress tab that shows that it looped through our 3 files that were in the directory, highlighted in yellow.

mf18a

mf18mf19

 

And there you have it.  Now you know how to load multiple files into the same table without knowing the individual file names.

 

 

 

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