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.
Right click on the connection managers window. Select New Connection.
Select MULTIFLATFILE connection manager and click Add.
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.
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.
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.
Select the connection manager we just defined and click Ok.
Add a OLE DB Destination.
Point it to the table you want to import the data into.
Set up the column mapping.
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.
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.
Drag a Derived Column task and connect it between our source and destination.
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.
Now just double double click on the OLE DB Destination and map our new column with file name to the destination table.