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.

27 comments:

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

    ReplyDelete
  2. 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

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

    ReplyDelete
  4. 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.

    ReplyDelete
  5. 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

    ReplyDelete

  6. 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

    ReplyDelete
  7. 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

    ReplyDelete


  8. 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
    soilplantfood.com


    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
    soilplantfood.com

    ReplyDelete
  9. 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

    ReplyDelete
  10. Thanks for sharing the crack but you need to update this version because here new version Available below;

    https://licensedinfo.com/microsoft-tab-enterprise-crack/

    ReplyDelete
  11. This site have particular software articles which emits an impression of being a significant and significant for you individual, able software installation.This is the spot you can get helps for any software installation, usage and cracked.
    Guitar Pro Crack
    Serato DJ Pro Crack
    Aiseesoft FoneLab Crack
    Rosetta Stone Crack
    Mirillis Action Crack

    ReplyDelete
  12. Thanks For Sharing, Here New Version Available;

    https://licensedinfo.com/microsoft-tab-enterprise-crack/

    ReplyDelete
  13. I guess I am the only one who came here to share my very own experience.
    Guess what!? I am using my laptop for almost the past 2 years, but I had no idea of solving some basic issues.
    I do not know how to Download Cracked Pro Softwares But thankfully, I recently visited a website named procrackhere.com

    Tenorshare ReiBoot Pro Crack
    DeepL Pro Crack
    Awesome Miner Crack

    ReplyDelete
  14. Is this a paid topic or do you change it yourself?
    However, stopping by with great quality writing, it's hard to see any good blog today.

    Mirillis Action Crack

    ReplyDelete
  15. Thank you for sharing the great information with us. this blog is
    very good and informative.
    สมัคร igoal

    ReplyDelete
  16. Welcome To Techbrothersit: Ssis - How To Load Multiple Files ( .Txt Or .Csv ) To A Table With File Names >>>>> Download Now

    >>>>> Download Full

    Welcome To Techbrothersit: Ssis - How To Load Multiple Files ( .Txt Or .Csv ) To A Table With File Names >>>>> Download LINK

    >>>>> Download Now

    Welcome To Techbrothersit: Ssis - How To Load Multiple Files ( .Txt Or .Csv ) To A Table With File Names >>>>> Download Full

    >>>>> Download LINK Rr

    ReplyDelete
  17. Welcome To Techbrothersit: Ssis - How To Load Multiple Files ( .Txt Or .Csv ) To A Table With File Names >>>>> Download Now

    >>>>> Download Full

    Welcome To Techbrothersit: Ssis - How To Load Multiple Files ( .Txt Or .Csv ) To A Table With File Names >>>>> Download LINK

    >>>>> Download Now

    Welcome To Techbrothersit: Ssis - How To Load Multiple Files ( .Txt Or .Csv ) To A Table With File Names >>>>> Download Full

    >>>>> Download LINK 95

    ReplyDelete
  18. Wonderful blog! I loved the way you gave us such information about this post. And a blog is really helpful for us for this website.

    3d hologram fan

    ReplyDelete
  19. I guess I am the only one who came here to share my very own experience. Guess what!? I am using my laptop for almost the past 2 years, but I had no idea of solving some basic issues. I do not know how to Crack Softwares Free Download But thankfully, I recently visited a website named ProCrackHere
    AirmyPC Crack
    K7 Total Security Crack

    ReplyDelete
  20. Printway provides its customers a varied suite of printed cards. We have a history of over 6 years in the printing industry. We have produced business cards, pamphlets, flyers, brochures, etc.Print Way

    ReplyDelete
  21. Dear Sir,
    Suppose I use this method to upload multiple files to SQL. How do you propose to get all processed files one by one to Processed folder. I have tried your method on this website to move file from one folder to another. However, it gave me some or other issue and I had to drop the idea.

    If you could provide the extension to this flow, to move successfully processed file one by one to other folder, it will be of great help.

    Regards.
    Digvijay

    ReplyDelete