Saturday, March 5, 2011

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

No comments:

Post a Comment