The For Loop

The For Loop

The For Loop is really a pretty simple container. It is useful when you need to execute the same workflow repeatedly for a given number of times. It can also be used to execute what are called "forever loops" to do things like polling for an event or to repeat some action continually when you don't know termination count or time frame. For example, some have used this loop to watch for new files to appear in a folder with a task and then process the file with the rest of the workflow before returning to the start of the loop and waiting for the next file. Whether you need to execute a given control flow a predetermined number of times or until a variable has a certain value, the For Loop is for you.

The For Loop has three expressions that control its execution and termination. Figure 13.1 shows the For Loop designer with a basic setup.

Figure 13.1. The For Loop uses three expressions to control execution

As you can see in Figure 13.1, there are two tabs, the For Loop tab and the Expressions tab. The Expressions tab is generally covered in Chapter 9, "Using Expressions." However, to be clear, that tab is the standard way to add property expressions and is not related to the Init, Eval, and Assign expressions the For Loop provides to control execution.

InitExpression

InitExpression is an abbreviated name for Initialization Expression. The For Loop evaluates the InitExpression exactly once at the start of the For Loop execution. It's useful for initializing values of variables that you use in the other two expressions, but it can also be used to evaluate variable values that are modified with the For Loop control flow.

EvalExpression

EvalExpression is an abbreviated name for Evaluation Expression. The For Loop evaluates the EvalExpression for every iteration of the For Loop. It is evaluated at the top of the loop, which means it is evaluated before the control flow in the For Loop Container is executed but after the InitExpression. So, it's possible to have a valid For Loop that executes successfully without any of its contents executing.

For example, if you set the InitExpression to be

@LOOPINDEX = 1 

and the EvalExpression to be

@LOOPINDEX < 1 

the For Loop executes successfully, but it does not execute its contents.

Tip

The EvalExpression must evaluate to a Boolean value. In other words, it must use one of the Boolean operators such as <, >, ==, or trUE, and so on. In fact, if you want to create a "forever loop," you can set the EvalExpression to trUE.

AssignExpression

AssignExpression is an abbreviated name for Assignment Expression. This expression is evaluated at the bottom of each iteration and you generally use it to increment the index value so that the loop will terminate.

In the sample solution, S13-Loops, the package called ForLoop.dtsx contains the simple For Loop shown in Figure 13.1. Inside the loop is a message box that shows the current value of the variable used to hold the index value called LOOPINDEX. try changing the expressions and running the package to see the effects your changes have on the For Loop execution.

Note

The For and Foreach Loops bear a remarkable resemblance to some programming language intrinsics called For, Foreach, Do While , and While Loop s, and that's no coincidence. These language features have been around as long as structured programming and so have proven their value. The For and Foreach Loops were modeled on their programming language counterparts.

A While Loop

In Integration Services, the For Loop is really the equivalent of a While Loop. A common question is: "How do you create a While Loop in a package?" Both the While Loop and the For Loop continue to loop until an expression evaluates to FALSE. Perhaps the main difference is that most people think of the For Loop as self-terminatingin other words, the loop terminates after so many iterations as defined by the expressions and that the expressions really determine if the loop terminates. On the other hand, many people think of a While Loop as being terminated by some change that happens external to the loop, such as an event or a variable value changing. While Loops in procedural programming typically have a break statement.

The For Loop supports both models and although it is common to think of the assignment and initialization expressions as ways to initialize and increment an index variable, they are not required and any variable can be used as a pseudo break statement to determine if the For Loop should terminate.

Therefore, to set up a While Loop in the For Loop, simply create an expression that tests a variable that can be modified by some workflow either inside or outside the For Loop. If, like a break statement, you also want to bypass workflow in the For Loop as soon as the variable changes, you can use expressions on precedence constraints that test the same variable you use to terminate the loop.