- ForEach Loop Conainter works same as For Loop Container, but only difference is - it will loop the flow
for each item within a collection
- Here collection can be set of files, ADO.Net resultset or anything
- Have 3-4 .txt files with simple ID,Name column and 2-3 rows in each file.
- You can put those files in different folder also
- In database, execute the following code
CREATE TABLE ForEachLoopDemoFiles (ID INT, FilePath VARCHAR(255), FileName VARCHAR(50))
INSERT INTO ForEachLoopDemoFiles (ID, FilePath, FileName)
create TABLE ForEachLoopDemoLoad (ID int, Name varchar(10), FileInfo nvarchar(255))
How to deal with it?
- Create 3 variables.
- In control flow task, create 1 Execute SQL Task which goes further to For Each Loop Container which has internally Data Flow Task
- In Execute SQL Task, set the following property. Resultset should be Full result set
- In Execute SQL Task, set the result set like this. So whole result set will be stored in FileInfo object variable
- In Foreach loop editor, set the following properties
- In Foreach loop editor, few more settings in Variable Mappings
- Data Flow Task should look like this
@[User::FilePath] + "\\" + @[User::FileName]
We can use Expression builder also for this.
- Derived column we can set like this.
- In Destination, we can do normal mapping
- Run the package and check the result in "ForEachLoopDemoLoad" table