For Loop Container
The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.
The For Loop container uses the following elements to define the loop:
- An optional initialization expression that assigns values to the loop counters.
- An evaluation expression that contains the expression used to test whether the loop should stop or continue.
- An optional iteration expression that increments or decrements the loop counter.
The following diagram shows a For Loop container with a Send Mail task. If the initialization expression is @Counter = 0, the evaluation expression is @Counter < 4, and the iteration expression is @Counter = @Counter + 1, the loop repeats four times and sends four e-mail messages.
.gif "A For Loop container repeats a task four times")
The expressions must be valid SQL Server 2005 Integration Services expressions. For more information, see Integration Services Expression Reference.
To create the initialization and assignment expressions, you can use the assignment operator (=). This operator is not otherwise supported by the Integration Services expression grammar and can only be used by the initialization and assignment expression types in the For Loop container. Any expression that uses the assignment operator must have the syntax @Var = <expression>, where Var is a run-time variable and <expression> is an expression that follows the rules of the SSIS expression syntax. The expression can include the variables, literals, and any operators and functions that the SSIS expression grammar supports. The expression must evaluate to a data type that can be cast to the data type of the variable.
A For Loop container can have only one evaluation expression. This means that the For Loop container runs all its control flow elements the same number of times. Because the For Loop container can include other For Loop containers, you can build nested loops and implement complex looping in packages.
You can set a transaction property on the For Loop container to define a transaction for a subset of the package control flow. In this way, you can manage transactions at a more granular level. For example, if a For Loop container repeats a control flow that updates data in a table multiple times, you can configure the For Loop and its control flow to use a transaction to ensure that if not all data is updated successfully, no data is updated. For more information, see Incorporating Transactions in Packages.
Configuring the For Loop Container
You can set properties through SSIS Designer or programmatically.
For more information about the properties that you can set in SSIS Designer, click one of the following topics:
For more information about setting these properties in SSIS Designer, click one of the following topics:
Configuring the For Loop Container Programmatically
For more information about programmatically setting these properties, click the following topic:
See Also
Concepts
Creating Package Control Flow
Integration Services Containers