Home > SSIS > SSIS Import Multiple Files into a Table

SSIS Import Multiple Files into a Table

I have been banging my head against the computer screen today, fighting with SSIS, which can be incredibly unintuitive. Anyway, as usual, the internet eventually came to my rescue so I can post the solution.

My problem was passing the file name as a parameter into a Data Flow Task. The Data Flow Task was part of a For Each Loop, which worked perfectly except from the Data Flow Task, which was hardcoded with the file name. The solution came from http://aspnetcomplete.blogspot.co.uk/2012/08/ssis-package-using-flat-file-source-to.html and here it is (in case that link breaks in future):

“I recently had a request to import multiple csv files into a database table then move the files to a new folder. Since this was the first time I was using SSIS Packages I had to do a bit of googling to get my head around it. I found a bunch of blogs and other articles on how to do things in SSIS but none of these posts gave me exactly what i required. So i sat down and knuckled in and eventually figured out how to do this using variables. Below is how you can do this:

This article requires you to have the basic knowledge of how to use the IDE for SSIS packages.

First off we need to create our directories

  • Create a folder c:\temp
  • Create a folder c:\temp\data
  • Create a folder c:\temp\data\Archived

Next we need create our variables and set their settings as the diagram below shows.

  1. varSourceFolder
  2. varArchiveFolder
  3. varArchivePath
  4. varFileName
  5. varFilePath


Now on the Control flow place the following items and configure them as the screenshots below show.

  • Foreach Loop Container
  • Data Flow Task (Place inside the Foreach Loop Container)
  • File System Task  (Place inside the Foreach Loop Container)


Now double click on the Data Flow Task, this will take you to the data flow tab.
On the Data Flow place the following items.

  • Flat File Source
  • Ole DB Destination

Now we need to configure the connections for our source file and our destination

  • Double click on the Flat File Source and create a new connection
  • Give the connection a name and browse to a file in the “c:\temp\data” folder (in my case i have a CSV file)
  • Set any other options you may require (in my case I changed the Header row delimiter to | as my CSV is pipe delimited).
  • Select “Columns” and click “Ok”

In order to make the FlatFile connection dynamic we have to configure the ConnectionString Property as an Expression. varFilePath will be updated on each iteration of file in the Foreach Loop Container.

On the connection managers window you will see the connection you have just created.
Select this connection and in the properties pane select expressions and configure the expression as shown in the screenshot below.


  • Now double click the OLE DB Destination and create a new connection.
  • Select the data connection you want to use or create a new one
  • Select your data access mode (mine is table or view)
  • Select the name of the table or view or press new (pressing new will create a new table or view based on the data in your file)
  • Select mappings on the left and map the columns from your file to your database table.
  • Click “Ok”

Remember to link all your items together as seen below.


Now run your package and check the results.

What did we do?

We have just created a SSIS package that looks in a source directory for all files of extension CSV. The package will iterate through each file and import the data in the file to a SQL table in our database, the package will then move the file to the archive directory.

How can I see my results?

You can run a select query on your table in your database and see if you have the expected data. You can also check your c:\temp\data folder and you should notice the files are no longer there, they should now be in the c:\temp\data\Archived folder.

I hope this help you in your experience with SSIS.

Categories: SSIS Tags: , ,
  1. Bryce
    November 22nd, 2013 at 00:50 | #1

    Awesome! this is exactly what I was looking for. I think I saw the original article as well, but it wasn’t explained as well as it is here.

    Just one question…the last image in this article shows a Flat File Source linked to the OLE DB Destination…but how does this relate to the image above it? Is it a part of the forEach loop container?


  2. Bryce
    November 22nd, 2013 at 16:51 | #2

    nvm…I see it now. The second picture is the data flow.

  3. Liz
    December 17th, 2013 at 06:48 | #3

    I can’t thank you enough for posting this! I have to import multiple flat files every week with the same 15 columns that have to be defined every time…. it’s awful. Now, instead of using the Text Import Export Wizard I get to use a package that loops through all the files and uploads them. All I have to do is put the files in the folder and press the Execute button. I think you showed that silly Import Wizard what true magic is!

  4. Iryna
    December 11th, 2016 at 21:41 | #4

    Just wanted to thank you for the article! Perfect explanation.

  5. Roland
    March 7th, 2017 at 10:01 | #5

    Thanks!! Your article finally allowed me to do this after many hours of fiddling around in visual studio.

    I would like to do the same with excel files. I add Excel Source instead of flat file source but when setting the ConnectionString property for the excel connection manager as @[User::varFilePath] I get the following error:

    “The connection string format is not valid. It must consist of one or more components of the form X=Y…”

    What should I do to get this working?

  6. Roland
    March 8th, 2017 at 12:28 | #6

    I can say that I found the solution on this page:


    I successfully combined this solution with your explanation how to move the files to an archive folder.

  7. J.D.
    March 20th, 2017 at 20:26 | #7

    Can you use the file variable name inside the data flow task? I’d like to use it to place the file name in every single record.

  1. No trackbacks yet.