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