SSIS - How To Load Multiple Files ( .txt or .csv ) To a Table With File Names

Scenario: 

We receive sale files for different regions ( Asia, Europe, North America) in our Source Folder. We need to load these files to RegionSale Table with File Name and Folder Path. In this example the files are .txt but they can be .csv.

Here is Sample data for each of the file that I am using 

Asia_Sale_20131224.txt

RegionCd,SalePersonName,SaleAmt

AS,Aamir,100

AS,Raza,200

AS,Sukhjeet,300

Europe_Sale_20131224.txt

RegionCd,SalePersonName,SaleAmt

EU,Mike,1000

EU,Robert,1500

NorthAmerica_Sale_20131224.txt

RegionCd,SalePersonName,SaleAmt

NA,Jimmy,700

NA,Lisa,200

Solution : 

We will be using Foreach Loop Container to loop through these files and then use Derived column to generate a new column for File Name with Folder Path. Let's start with step by step approach

Destination Table DDL 

Create table dbo.RegionalSale

 (RegionSaleID int identity(1,1),

 RegionCd Char(2) ,

SalePersonName Varchar(50) ,

SaleAmt INT,

FileQualifiedPath VARCHAR(100))

Step 1:

Create new SSIS Package. Create variable VarSourceFolder and set the value of this variable to your folder in which files exist. I created the variable so if we have to change the Source Folder path , we can do it by using SSIS Configuration for this variable.

Step 2: 
Bring Foreach Loop Container to Control Flow Pane and Set Directory=VarSourceFolder as shown below. As we are going to read only .txt files. Provide *.txt under Files text box.

Change *.* to *.txt as shown in #2 to only read .txt files. Also choose Full qualified to get complete path of file on each iteration.

Go to Variable Mapping and create a new variable VarFilePath that will hold fully qualified path on each iteration.

Step 3:
Bring Data Flow Task inside Foreach Loop Container and open it by double clicking. Inside Data Flow Task,Drag Flat File Source and create connection string by using any file from Source Folder.

Click on Columns to make sure everything working as expected. You can preview some data by clicking on Preview.

Step 4:
Drag Derived column Transformation and connect Flat File Source to it. Generate a new column by using the VarFilePath as shown below

Expression :(DT_STR,100,1252)@[User::VarFilePath]
I have converted the value of variable to Varchar(100) by using Cast(DT_STR,Length,Code)

Step 5:

Drag OLE DB Destination and connect Derived Column Transformation to it and choose dbo.SaleRegion as our destination table and map the input column to destination columns.

Step 6:
As we have created Flat File Connection to Asia file but the file name will be changing with each iteration so we have to go to Flat File Connection and write expression for connection string to use the VarFileName variable.

Set DelayValidation to True.

Step 7:
Run the SSIS Package and check if the data from all files are loaded to table.

All the files are loaded successfully with fully qualified path.