For Each Loop Container in SSIS

For Each Loop Container in SSIS:

Why For Each Loop Container Task is Use ?

• For Each Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
• In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• The for each loop container acts as a repeating control flow in a package. Its operations are similar to work of For each keyword in any advanced programming language. We have a definite type of enumerator for each type of objects.
• Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Enumerator is nothing but an iterator where an object that enables a programmer to traverse a container

SQL Server Integration Services provides the following enumerator types:

1. Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
2. Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
3. Foreach ADO Enumerator: Useful for enumerating rows in tables.
4. Foreach ADO.NET Schema Rowset Enumerator: To enumerate through schema information about a data source. For example, to get list of tables in a database.
5. Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
6. Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
7. Foreach SMO Enumerator: It enumerates through SQL Server Management Objects (SMO) objects.

How For Each Loop Container Task is look like ?

This is Second task in tab itself !!

Drag this out in your development plane

Features of For Each Loop Container Task

The following diagram shows a Foreach Loop container that has a File System task. The Foreach loop uses the Foreach File enumerator, and the File System task is configured to copy a file. If the folder that the enumerator specifies contains four files, the loop repeats four times and copies four files.

Where we can use a combination of variables and property expressions to update the property of the package object with the enumerator collection value. First you map the collection value to a user-defined variable, and then you implement a property expression on the property that uses the variable.

Enumerator Types

How to use For Loop Container Task ?

We can configure For Each Loop Container in following three ways

1. SSIS Foreach Loop Container – General
2. SSIS Foreach Loop Container – Collection
3. SSIS Foreach Loop Container – Variable Mappings

SSIS Foreach Loop Container – General

Use the General page of the Foreach Loop Editor dialog box to name and describe a Foreach Loop container that uses a specified enumerator to repeat a workflow for each member in a collection.

In this tab we can just specify name of for each loop container as we required :

SSIS Foreach Loop Container – Collection

Here we can actually select various enumeration and iterate accordingly

See the following list when we select collection tab and Click on dropdown Enumerator

Following three way to configure this tab :

1.Select Enumeration Type

2.Select Path path or variable list for main Enumeration in 7 option

Here we are getting different option for every enumeration

(I) For each File Enumeration

Select Path of the file and type/extension for required file to traverse

(II)For Each Item Enumerator

Select or defined the item as show in configuration plan

 

(III)For Each ADO Enumerator

In this we have three option as follows

(IV)Foreach ADO.NET Schema Rowset Enumerator

To enumerate through schema information about a data source. For example, to get list of tables in a database.

(V)Foreach From Variable Enumerator

Here we have choose variable or we can defined new variable too

(VI)Foreach NodeList Enumerator

Used to enumerate the result set of an XML Path Language (XPath) expression.

(VII)Foreach SMO Enumerator

It enumerates through SQL Server Management Objects (SMO) objects.

In Short Select the enumerator type from the list. This property has the options listed as follows:

1. Foreach File Enumerator: Enumerate files
2. Foreach Item Enumerator: Enumerate values in an item
3. Foreach ADO Enumerator: Enumerate tables or rows in tables
4. Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
5. Foreach From Variable Enumerator: Enumerate the value in a variable
6. Foreach Nodelist Enumerator: Enumerate nodes in an XML document
7. Foreach SMO Enumerator: Enumerate a SMO object

SSIS Foreach Loop Container – Variable Mappings

In this last tab we are going to just map the variable as we required

As I have map Var:User:MyFirstVar in index 0

Various Properties for For Each Loop Container Task ?

Following are various configurable properties for For loop container

Things to remember For Each Loop task

This is iterative task so where ever you will find things to iterate over file of variable we

are going to use for each loop task

Reference link for For Each Loop Container Task

http://msdn.microsoft.com/en-us/library/ms141724.aspx

http://msdn.microsoft.com/en-us/library/ms177187.aspx

http://msdn.microsoft.com/en-us/library/ms187670.aspx

http://msdn.microsoft.com/en-us/library/ms188528.aspx

SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow « Sherry’s BI Corner

Programming Foreach Loop Container – Enumerating Excel Files | SQL Lion

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61987/

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx