Foreach Loop Container

Foreach Loop Container

Ø  Foreach loop Container defines a repeating control flow in a package.

Ø  It is used to iterate through collection of object.

Ø  MSBI provide the following type of enumerator.

o   Foreach file enumerator

o   Foreach Item enumerator

o   Foreach ADO enumerator

o   Foreach ADO.NET schema rowset enumerator

o   Foreach from variables enumerator

o   Foreach node list enumerator

o   Foreach SMO enumerator

Foreach file enumerator

The Foreach file enumerator loops through a collection of files within a folder and make it possible to execute.

 For example:-

In a folder we have multiple text document and we want to store the text data in database.

Uploading multiple text data in database

To use the package you need to place the 3 enclosed text files into your "C:\Temp" folder and make sure there are no other "*.txt" files in there. Then simply run the package. It does a number of things:

1. Enumerates all the files in "C:\Temp" matching the wild carded string "*.txt"
   

2. Maps the file path of the currently enumerated file to a variable User::FileName
   

3. Uses a property expression on the Connection String property of a Flat File Connection Manager to point to the file indicated by User::FileName
   

4. Process the file (which in this demo simply passes all the data into a Recordset destination)
   

I hope this alleviates some of the pain people are having in using the file enumerator in the Foreach container. It’s a wonderfully powerful feature of SSIS and will have many uses as SSIS gets unleashed unto the masses.

bagi_01.txt

CustomerName,PhNo,email

AAA,111-222-3000,aaa@test.com

BBB,111-222-3000,bbb@test.com

CCC,111-222-3000,ccc@test.com

DDD,111-222-3000,ddd@test.com

Bagi_02.txt

CustomerName, PhNo,email

EEE,111-222-3000,eee@test.com

FFF,111-222-3000,fff@test.com

GGG,111-222-3000,ggg@test.com

HHH,111-222-3000,hhh@test.com

DataBase Table

CREATETABLE [dbo].[Staging_Customers](

      [CustName] [varchar](255)NULL,

      [CustPhone] [varchar](255)NULL,

      [CustEmail] [varchar](255)NULL

)ON [PRIMARY]

Import Data from Multiple Excel Files using SSIS

Create new integration Service Project, create a new
package say “ImportMultipleExcelFiles.dtsx”

 1. Create a Folder Excel Files (C:\ExcelFiles)and then create multiple
Excel files in folder C:\ExcelFiles but make sure all Excel files have identical schema (format) 

 Assuming all Excel files above contain data in worksheet “Sheet1”
with below schema

City	Value
AY	2000
BY	3000
CY	4000

You can have your own sheet name but keep a note, worksheet
name has to be uniform across all Excel files that contains data to be uploaded.

 2. Right Click on Control Flow
Window, Select Variables

Add a variable “FileName” at Package Level having data type string

  

3. Go to Control Flow add a “For Each Loop Container” Component.

 Right-click on the Foreach Loop container and select Edit.
Then, Click on Collection “Collection” tab. Assign folder path and file type as
shown below

Then go to “VariableMappings“ tab and map variable created above like
below

6. Drag a “Data Flow Task “inside “Foreach Loop Conatainer”, double clickson Data flow task

Drag one “Excel Source” task ,double click on this to get “Excel Source Editor “ Window

Now choose new and new window will open , now browse to your first excel file that is C:\ExcelFiles\First.xls

Under “Name of the Excel Sheet” on “ExcelSource Editor “, choose Sheet1$

7. Now drag “OLE DB Destination” task  , connect “Excel Source” to “OLE DB Destination”

Point this connection to your database and create new table or use an existing table

Map both like below

8. Go to the Properties of ”Excel Connection Manager” Expand “Expressions” Choose “Connection String” property and assign value of Expression like below

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +“;Extended Properties=\”Excel 8.0;HDR=YES\”;”

9. Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

10.Set property DelayValidation=TRUE on the Data Flow task

10.Execute your Package

Foreach Item Enumerator

Ø  This list is static at execution time. The list item cannot be dynamically from a configuration file or a source like a sql table.

Ø  This enumerator type is not used too often in package

Foreach ADO enumerator

Ø  The most useful type of enumerator

Ø  Loops through a data table that loaded into an object type variable

Ø  Data table can be loaded from Execute SQL Task with Full Result Set

Ø  Data table can be loaded from Data Flow Task with Record Set Destination

Advantages of ADO Enumerator

Ø  Data Table can be loaded from any source through the Data Flow (Excel, Oracle, DB2, Flat File…)

Ø  Data Table can be transformed through any transformation through the Data Flow

Ø  Loop structure will be dynamic, because the source table that the data table comes from can be changed

Example: - here I am going to create a simple package which include execute SQL task Foreach loop and a script task. We are getting a dataset form the Execute SQL task and receive the data set value in the ADO Enumerator and display the one by one value using script task.

In Enumerator select Foreach ADO Enumerator.

Ado object source variables: - select the dataset object which is declared for receiving the result of sql task.

Map the dataset value to the variables.

Click ok.

Inside the Foreach loop container we take a script task. In read only variable we pass the variables which are receiving from the Foreach loop container.

Click on Edit Script you will the following screen.

You can write the c# code.

For getting the variables values using Dts.Variables["EmployeeID(VariableName)"].Value.ToString()

Save it and close it. Now execute the Package.

ADO.NET Schema Rowset Enumerator

Ø  Looping through schema and metadata of the data source

 Examples;

o   All tables under specific database

o   Views of Sales schema under the database

o   All system objects

o   List of items can be filtered

o   INFORMATION_SCHEMA views in SQL Server

Foreach Node list Enumerator

Ø  Looping through elements and attributes of an xml data

XML can be

o   DirectInput -> Static loop

o   File Connection -> Configurable loop

o   Variable -> Dynamic loop

Xpath enumeration types

Ø  Navigator: Looping based on .NET XPathNavigator

Ø  Node: Looping based on Xpath provided and returns Nodes

Ø  Node Text: Looping based on Xpath provided and returns Node Texts

Ø  Element Collection: looping through elements provided through OuterXpath, and fetch data based on InnerXpath

Foreach SMO Enumerator

Ø  SMO = SQL Server Management Objects

Ø  List of .NET libraries for managing SQL Server objects.

Ø  Examples:-

o   Looping through SQL Server jobs, Logins

o   Linked Servers

Foreach SMO Enumerator: Sample

Ø  There are two database servers for customer info; North Island, and South Island. Two Linked Server created to connect to each of these servers.

Ø  Loop structure required to loop through these linked servers (and any future linked servers with customer info), and load Customer data from Customer Info table into an integrated database.