Search This Blog

Foreach Container in SSIS with example

I often see  Developers having issues or confusion with foreach container, Sometimes they faced issue with looping the files or asking questions on how to process each file and load each file records in the destination table

So below I will create a simple package to demonstrate the  use for each loop container which will process 3 text files and using data flow we will process each file and will insert Record into destination table. For Reference I have added the all 3 data files in the last.

Below are the steps 

First I have created three small text files and have kept in  SSIS. folder with Name (File1, File2 and File3)

Next I am creating a destination table in SQL where the three files data will get loaded

Create table EmployeeDetail
(ID varchar(100),
 Name varchar(100),
 Salary varchar(100)
)

Now Open BIDS

 –Add a SSIS Project

-Add a for each container in you Package

Add a variable SourcePath which will have the folder location where all files has been placed.

Add a variable FileName that will be a file Name as in screenshot.

Next double Click on For Each Loop

Click on collection

Go to expression

Select Directory and add variable source path as below screenshot


Next click on variable mapping

Map the FileName  variable as below Screenshot.


Once done click ok

Next Drop a Data flow task Inside Foreach container.

Double click on Data flow task

Add a Flat file Source and create a flat file connection 

Next select flat file connection properties by pressing F4 

Now Go to expression as highlighted in yellow and Select connection string and add file Name variable as in Screenshot

Click OK

Add an ole-db destination

Connect the Flat file source to SQL  destination and click on mapping in SQL Destination

Run the Package and It will run successfully without any Issue.

,
Once completed you will see all 3 files data will get loaded into your table.Do a check by doing 

Select  * from EmployeeDetail

Below I am sharing the three file test data so that you can use these file to test for each container in SSIS.


File1.txt

ID,Name,Salary
6,Vikas,1000
7,Amit,1100

File2.txt

ID,Name,Salary
6,Vikas,1000
7,Amit,1100

File3.Txt

ID,Name,Salary
4,A,4000
5,B,7000

Please write in comment box if in case you will face any Issue while performing the above steps, I will be happy to help you.