Continuing from my last post on SSIS Control flow task in which we talk lot about For Loop Container:
MSBI # 22 – SSIS # 9 – Control Flow Tasks # 4 – For Loop Container Task « (B)usiness (I)ntelligence Mentalist
We are covering following points in this article
- Why For Each Loop Container Task is Use ?
- How For Each Loop Container Task is look like ?
- Features of For Each Loop Container Task
- How to use For Each Loop Container Task ?
- What is alternative For Each For each Loop Container Task ?
- Demonstration of using For Each Loop Container Task ?
- Various Properties For each For Each Loop Container Task ?
- Reference link For each For Each Loop Container Task
Now lets go through each aspects of For Each Loop Container Task :
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:
- Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
- Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
- Foreach ADO Enumerator: Useful for enumerating rows in tables.
- 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.
- Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
- Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
- 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.
How to use For Loop Container Task ?
We can configure For Each Loop Container in following three ways
- SSIS Foreach Loop Container – General
- SSIS Foreach Loop Container – Collection
- 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:
- Foreach File Enumerator: Enumerate files
- Foreach Item Enumerator: Enumerate values in an item
- Foreach ADO Enumerator: Enumerate tables or rows in tables
- Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
- Foreach From Variable Enumerator: Enumerate the value in a variable
- Foreach Nodelist Enumerator: Enumerate nodes in an XML document
- 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
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
Thanks for visiting my blog !!
Hope you have understood various aspect of For Each Loop Container Task and ready to use every aspects for same
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
Link Resource # 17 : August 16 – August 18 « Dactylonomy of Web Resource
5 thoughts on “MSBI # 23 – SSIS # 10 – Control Flow Tasks # 5 – For Each Loop Container Task”