Using For Loop and Foreach Loop Containers in SSIS

 

SQL Server Integration Services supports two container tasks name For Loop and Foreach loop. These containers supports to execute the repetitive workflows in controlled way. The For Loop has three expressions that control it’s execution and termination. This post also discuss about Foreach loop container in SSIS.

The three expressions are

InitExpression  The For Loop evaluates the InitExpression exactly once at the start of the loop execution. The initialized variables are used later in other expressions.

EvalExpression The For Loop evaluates the EvalExpression once for every iteration of the For Loop.For Example The  InitExpression would be @Index = 1 and EvalExpression would be @Index > 1. EvalExpression must use boolean operators.

AssignExpression This expression is used at bottom of each iteration.You use this expression to increment the index value so that loop terminates.

Foreach Loop – You can use Foreach Loop to process collection in the control flow. Foreach Loop designer contains two elements collections and variable mappings.

Each enumerator in Foreach Loop having it’s own designer as shown above. The variable mappings tab allows you to map the value from Foreach enumerator from variable.

If Foreach Enumerator enumerates only collections then index is always zero. If there are more than one element in your collection then you can use the variable names accordingly. Example: If there are two elements in collection then 0 for first element and 1 for second element.

Foreach Enumerators

Integration Services provide Foreach Enumerators each one has specific functionality.

Foreach File

Foreach File enumerator can be used to enumerating the files in directory. Once you specify the folder then you can set the following properties

You can specify which files to enumerate in directory by specifying in wildcards.

You can specify fully qualified name or just filename with an extension or base file name without an extension.

Foreach Item

It allows you to create custom collection and enumerate over the collection. You can specify columns and column types and their values.

Foreach enumerator is useful when you have non-uniform inputs you want to enumerate over time. You can use this enumerator when you want to process files from same locations, perform maintenance task on same servers.

Foreach ADO

Foreach ADO enumerator can be used to iterate over ADO record set or ADO.NET dataset. Using this you can enumerate over multiple rows and multiple result-sets.

Foreach SMO

The Foreach SMO enumerator allows you to enumerate over objects such as tables,databases, servers and views etc. SMO is SQL Server Management Objects and is a management API for SQL Server.

SMO allows you to specify objects that you want process with URN.

Conclusion

For loops are useful when you need to iterate multiple times and terminates based on a variable which might change it’s value by inside or outside loop. The foreach loop provides you to iterate over collections of various types.

Share this post :