Foreach Loop Container in SSIS

- 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

EXAMPLE

Pre-requisite
- 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))

Go

INSERT INTO ForEachLoopDemoFiles  (ID, FilePath, FileName)

VALUES (1,'C:\ForEachLoopContainerDemo\Folder1','a.txt'),

(2,'C:\ForEachLoopContainerDemo\Folder2','b.txt'),

(3,'C:\ForEachLoopContainerDemo\Folder3','c.txt')

Go

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

- In Flatfile connection manager, go to properties --> Expression --> ConnectionString and set following value.

@[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