• Article

Adding Iteration to a Control Flow

Integration Services includes the For Loop container, a control flow element that makes it simple to include looping that conditionally repeats a control flow in a package. For more information, see For Loop Container.

The For Loop container evaluates a condition on each iteration of the loop, and stops when the condition evaluates to false. The For Loop container includes expressions for initializing the loop, specifying the evaluation condition that stops execution of the repeating control flow, and assigning a value to an expression that updates the value against which the evaluation condition is compared. You must provide an evaluation condition, but initialization and assignment expressions are optional.

The For Loop container provides no functionality; it provides only the structure in which you build the repeatable control flow. To provide container functionality, you must include at least one task in the For Loop container. For more information, see Integration Services Tasks.

The For Loop container can include a control flow with multiple tasks, and can include other containers. Adding tasks and containers to a For Loop container is similar to adding them to a package, except you drag the tasks and containers to the For Loop container instead of to the package. If the For Loop container includes more than one task or container, you can connect them using precedence constraints just as you do in a package. For more information, see Precedence Constraints.

Using Expressions in For Loop Configuration

When you configure the For Loop container by specifying an evaluation condition, initialization value, or assignment value, you can use either literals or expressions.

The expressions can include variables. The advantage of using variables is that they can be updated at run time, making the packages more flexible and easier to manage. The maximum length of an expression is 4000 characters.

When you specify a variable in an expression, you must preface the variable name with the at sign (@). For example, for a variable named Counter, enter @Counter in the expression that the For Loop container uses. If you include the namespace property on the variable, you must enclose the variable and namespace in brackets. For example, for a Counter variable in the MyNamespace namespace, type [@MyNamespace::Counter].

The variables that the For Loop container uses must be defined in the scope of the For Loop container or in the scope of any container that is higher in the package container hierarchy. For example, a For Loop container can use variables defined in its scope and also variables defined in package scope. For more information, see Integration Services Variables and Using Variables in Packages.

The SSIS expression grammar provides a complete set of operators and functions for implementing complex expressions used for evaluation, initialization, or assignment. For more information, see Integration Services Expression Reference.

To implement a For Loop container in a control flow

  1. Add the For Loop container to the package. For more information, see How to: Add or Delete a Task or a Container in a Control Flow.

  2. Add tasks and containers to the For Loop container. For more information, see How to: Add or Delete a Task or a Container in a Control Flow.

  3. Connect tasks and containers in the For Loop container using precedence constraints. For more information, see How to: Connect Tasks and Containers by Using a Default Precedence Constraint.

  4. Configure the For Loop container. For more information, see How to: Configure a For Loop Container.

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.