SSIS For Loop Container

SSIS For Loop Container

The For Loop Container is a Control Flow Container that provides similar looping functionality as the T-SQL WHILE statement. The For Loop Container will execute all the Control Flow Tasks placed inside its boundaries until the evaluation condition defined in the EvalExpression section evaluates to False.

Figure 1. For Loop Container



The For Loop Container runtime properties are defined in the For Loop Editor. The For Loop Editor is accessed by right-clicking the For Loop Container and selecting Edit or simply by double-clicking it.

Figure 2. For Loop Editor



In the For Loop Container you specify an initialization expression (InitExpression), an evaluation expression (EvalExpression) and an assignment expression (AssignExpression). The expressions defined in the For Loop Editor are written using SQL Server Integrations Services Expression Language.

Table 1. For Loop Properties

 Property

  Required 

 Description

 Example

 InitExpression

 No

 Initializes variable for use as loop counter.   @MyVar = 1
 EvalExpression

 Yes

 Evaluates if loop should continue or stop. The expression must be a Boolean condition.
 Looping will stop once the expression evaluates to FALSE.
 @MyVar <= 5
 AssignExpression

 No

 Changes loop counter value after each iteration. Typically, the loop counter variable's
value is incremented or decremented.
 @MyVar = @MyVar +1







Following the examples from Table 1 above, the For Loop Container shown in Figure 3 will iterate through the three tasks exactly five (5) times in the order shown.

Figure 3. For Loop Container with three tasks



Sort by: Published Date | Most Recent | Most Useful
Comments
  • According to the syntax of SSIS expression language he assign operator is == (double equal sign

  • Yes, this is true in general for the SSIS Expression language, but the For Loop Container is the only exception in which this syntax changes to single equal operator "=" assignments and evaluations.

    Per BOL: "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. "

    Source: msdn.microsoft.com/.../ms139956.aspx

  • This is true in general for SSIS Expression Language. The For Loop Editor is the only place single equal operator "=" is used to evaluate or assign values.

    Per Books on Line: "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."

    Source: msdn.microsoft.com/.../ms139956.aspx

  • thanks!

Page 1 of 1 (4 items)