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.