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


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 



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.


  1. Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai

    aluminium scaffolding dealers in chennai

  2. Nice one and very useful for us.Thanks a lot and please keep sharing

  3. My rather long internet look up has at the end of the day been compensated with pleasant insight to talk about with my family and friends.
    CRM with Invoicing

  4. Thank you for this post. it's helping me so much.

  5. excellent blog site. Get super DIY scaffold towers from diy scaffold tower. We provide scaffold towers designed by British design with safety and durability in mind for the best user experience.

  6. Thank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.
    Best CRM System

  7. After looking through a few blog articles on your website,
    we sincerely appreciate the way you blogged.
    We've added it to our list of bookmarked web pages and will be checking back in the near
    future. Please also visit my website and tell us what you think.
    Eassos PartitionGuru Pro Crack
    Facebook Social Toolkit Crack
    Gather Proxy Premium Crack
    Office Tab Enterprise Crack
    Novicorp WinToFlash Professional crack


  8. I am very impressed with your post because this post is very beneficial for me and provide a new knowledge to me
    Ami Pro Crack

  9. Wonderful work! This is the kind of info that are meant to be shared across the internet. Disgrace on the search engines for not positioning this post higher! Come on over and consult with my website.
    So, I would like to Share Tenorshare ReiBoot Pro Crack with you.
    DAEMON Tools Pro Crack


  10. Nitrogen fertilisers and nitrogen are the most important for plants, animals, and human beings. It plays a direct role in the growth and developmental related processes of the plants.
    phenomenon of Brownian motion
    Amazing Health Benefits of Using Cinnamon
    Iron Biofortification of Plants for Improved Human Health

    Nitrogen fertilisers and nitrogen are the most important for plants, animals, and human beings. It plays a direct role in the growth and developmental related processes of the plants.
    Soil Fertility Management to Improve Food Production
    Calibration and interpretation of soil testing
    Food Scraps as Valuable Resources

  11. If you want to download the latest version of this software so links are given below!

    DAEMON Tools Pro Crack
    Altium Designer Crack
    Kaspersky Total Security Crack