Importing multiple text files using SSIS

We can import multiple flat files in one go using the MULTIFLATFILE connection manager. This can be used in scenarios where you have multiple files with the same format lying in a directory and you want to import records from all the files in one table.

First you need to make a connection for this.

New Connection
Right click on the connection managers window. Select New Connection.

MultiFlatFile Connection Manager
Select MULTIFLATFILE connection manager and click Add.

Connection Manager Editor
The connection manager editor window will open. Here you can specify the file path in the file names section. You can use wildcard characters to specify multiple files. The easiest way is to click on the Browse button and navigate to the folder where your files are located. Select any file from the folder and click Open and then replace the file name with wildcard character to specify multiple files, like *.* or in my case *.txt.

Connection Manager Editor Columns
Click on the Columns tab to specify your delimiters. You should get some records in the preview rows. Now click Ok to close this window.

Data Flow TaskFlat File Source
Drag a data flow task and double click on it to edit. In the Data Flow add a Flat File Source and double click on it to edit.

Flat File Source Editor
Select the connection manager we just defined and click Ok.

OLE DB Destination
Add a OLE DB Destination.

OLE DB Destination Editor
Point it to the table you want to import the data into.

OLE DB Destination Editor Column Mappings
Set up the column mapping.

Execute Package
That’s it! Now if you execute this package, you will see total records transfer to the table is equal to sum of number of records in each.

Adding a FileName column:
If you want to identify which file a particular record comes from, you can add a FileName column.

Advanced Editor for Flat File Source
Right click on the flat file source and select Show Advanced Editor. In the editor window click on Component Properties tab. Now specify a column name in the FileNameColumnName property. This will add an extra column to the Flat File Source with the file name for each row. This column will contain complete file path. We want only file name, also we will have to convert the data type of the column to DT_STR. We can do both by adding Derived Column.

Derived Column Task
Drag a Derived Column task and connect it between our source and destination.

Derived Column Transformation
Double click on the derived column task to edit. Set the properties as shown in the screenshot and click Ok. Here is the expression

SUBSTRING(FileName,LEN(FileName) - FINDSTRING(REVERSE(FileName),"\\",1) + 2,LEN(RIGHT(FileName,FINDSTRING(REVERSE(FileName),"\\",1) - 1)))

If you want complete file path then just enter FileName (which is our column name) as expression text.

OLE DB Destination Editor Column Mappings for FileName
Now just double double click on the OLE DB Destination and map our new column with file name to the destination table.

Download sample project

About Amit Singh

Software engineer; crazy about music, astronomy and radio controlled aircrafts. Big science/technology buff!
This entry was posted in Programming, SSIS and tagged , , , , , , , . Bookmark the permalink.

17 Responses to Importing multiple text files using SSIS

  1. designerplus says:

    Like your post and I see that you’re a programmer as well great! I have a open source internet business download site I’ve been working on thus far and a ftp commander to extract the files. I don’t know if this is a good ftp to work with at this point I’ll try it, maybe you can suggest a better ftp to use, I’m also using mysql I just need to put it all together to get it up and running. If you have any suggestions let me know. Much Thanks! Virginia

    • Amit Singh says:

      Hi, i have not work with an ftp client but i have this friend of mine who keeps on talking about an application called ipswitch ws_ftp. But i don’t think it is open source.

  2. jfbevilaqua says:

    Download link is broken. Please repost project.

  3. ToddT says:

    Thanks for the post… no idea a rapid fire file import was possible. This helped immensly!
    Todd

  4. Reggie says:

    Hi,
    I was wondering on how to implement the adding of the filename when my source is Excel?

    thanks in advance.

    Reggie

  5. Fausto says:

    Very helpful post!

  6. nojetlag says:

    How do you keep granular control over the files on import, for example when 2 of 8 files are not ok and will fail on import. How can you single them out and only move the successful imported ones into the archive folder (to not get them double into the database upon rerun of the import process) ?

  7. taerun says:

    Your post was very helpful especially with all the screen shots. I had the same question of finding the failed ones.

  8. Jay says:

    Amazingly useful. Love it.

  9. Nice post !!! I am also working with SSIS and your experience helps me alot !!! My website is phpring.com/msbi . Can you suggest me what i should do next? I am a learner and will really appreciate your time !!!

  10. Pingback: Some business intelligence links I revisit and send to others | Data Savvy

  11. Very helpful post!, thank you.

  12. madivalappa patil says:

    This is very helpful post and thanks for detailed explanation.

  13. Thank you very much! Had no idea!

  14. vasu says:

    Thanks, I spent 3 days, before this blog.

  15. Melanie Peterson says:

    You just saved my bacon. 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