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.

 

 

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

 

 

Running the package goes without any issues.

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

 

 

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.

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.

 

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.

 

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.

 

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.

 

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.

 

 

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

 

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.

 

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.

 

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

 

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

 

The files that got loaded.

 

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

 

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