SQL Server 2005 - SQL Server Integration Services - Loop Containers
July 8, 2005
As we briefly mentioned in the previous installment of this series, among features introduced in the SQL Server 2005 Integration Services there are For and ForEach loops, implemented in the form of containers that can be incorporated into the Control Flow part of a package design. (They are included in the list of package components listed in the Toolbox accessible from the Control Flow tab of the SSIS Designer interface of the SQL Server Business Intelligence Development Studio). Both of them serve the same generic purpose (familiar to anyone with even minimal exposure to programming languages), which is repetitive execution of a sequence of tasks, as long as an arbitrarily chosen condition is satisfied (status of the condition is checked at the end of each execution sequence). In the SQL Server 2000 (and 7.0) Data Transformation Services, such functionality was not available and emulating it required rather cumbersome workarounds.
In the case of the For Loop container, the decision of whether execution of components within its scope should be repeated is based on the value of three interrelated expressions:
All three properties must follow the syntax of SSIS expressions, which we
discussed in our previous
article. The only deviation from this rule is the use of assignment
operator, which is not allowed in any SSIS context other than the properties of
the For Loop container. Expression in this case takes the form
We will demonstrate the For Loop functionality through a very simple package, which, besides the For Loop container itself, also includes a single Script Task (within the container scope). We will also use a single variable iCounter of data type Int16 as the loop counter, serving as an element of InitExpression, EvalExpression, and AssignExpression expressions. To accomplish this, create a new (or use an existing) SSIS project within the SQL Server Business Intelligence Development Studio. Ensure that the Control Flow tab is selected and drag the For Loop Container icon from the Toolbox to the Designer area. With the newly created container selected, display the Variable window (this can be done from the View menu, pointing to the Other Windows submenu and clicking on the Variables entry). Clicking on the first icon in the Variables window toolbar will create a variable with the scope of the For Loop Container. In the Name column, type in iCounter and choose the Int16 from the drop-down list as the Data Type. Right-click on the For Loop Container and select Edit from the context-sensitive menu. Fill out the For Loop Properties section of the For Loop Editor according to the following list:
Click on OK to close the For Loop Editor. Next, drag a Script Task component from the Toolbox and drop it inside the For Loop container. Right-click on it and select the Edit item from the context-sensitive menu. You will be presented with the Script Task Editor interface, which is fairly straightforward. The portion that is relevant to us, accessible by clicking on the Script entry on the left hand side of the window, allows you to:
In our case, the only change to default settings
will be adding iCounter to the ReadOnlyVariables box. Actual code is accessible
by clicking on the Design Script... button in the lower right corner of the
Script Task Editor. This launches Microsoft Visual Studio for Applications,
where you will notice the ScriptMain window containing the public class with
the same name and its only Sub called Main. Within the code for Sub Main(), you
should see a commented out entry stating
Public Sub Main() ' ' Add your code here ' MsgBox(Dts.Variables("iCounter").Value.ToString) Dts.TaskResult = Dts.Results.Success End Sub
Close the Visual Studio for Applications window and click on the OK button in the Script Task Editor to return to the Designer interface. If you select the For Loop container and right-click on it, you will notice the Execute Task option in its context-sensitive menu (this allows you to launch individual tasks, without executing the entire package). Selecting it will result in the For Loop Container and the Script Task within it turning yellow, and shortly afterwards, a message box with the Script Task header, value 0, and OK button being displayed. After clicking on the OK button, you should see another message box with a value of 1, followed (after clicking on OK again) with another one with a value of 2. Confirming the message again will complete the execution of the For Loop (as expected, since the iCounter reached the value of 3, causing the EvalExpression to evaluate to False), which is indicated by the For Loop Container and Script Task changing their color from yellow to green. To stop execution, select the Stop Debugging from the Debug menu (or press the Shift + F5 key combination).
While For Loop container determines the number of iterations by checking whether an arbitrary condition evaluates to True or False, the ForEach loop derives this result through enumeration. Enumeration can be applied to a number of different collections, dependent on enumerator types:
We will provide another very straightforward example illustrating the operation of the Foreach Loop container. For the sake of simplicity, we will limit our focus in this article to Foreach File Enumerator, however we will be discussing the remaining ones in more detail in future articles. In order to test its functionality, you can take advantage of the same package that was used earlier when discussing the For Loop container. Start by dragging the Foreach Loop container icon from the Toolbox onto the Designer area of the SQL Server Business Intelligence Studio. With the container selected, create a new variable called sFileName of String datatype and Foreach Loop Container scope. Create a new Script Task (as before), but this time type in the sFileName as the ReadOnlyVariables entry. Modify the Sub Main() of the script so it contains the following code:
Public Sub Main() ' ' Add your code here ' MsgBox(Dts.Variables("sFileName").Value.ToString) Dts.TaskResult = Dts.Results.Success End Sub
Next, in the Foreach Loop Editor interface (which you can display by selecting Edit item from the context-sensitive menu of the Foreach Loop container), click on the Collection item (appearing on the left hand side of the Editor window). Set the Enumerator type to Foreach File Enumerator (by choosing the appropriate entry from the drop down list) and specify the name of a Folder or locate it after clicking on the Browse... button (this can be any of the folders on a local or remote computer that you have at least read access to). Provide the name of the files within this folder that you want to work with, and pick one of three options that control the retrieved file name (fully qualified, name only, or name and extension).
Finally, in the Foreach Loop Editor interface, click on Variable Mappings, and set the User::sFileName variable to Index 0. This will associate our variable with the first column (since the index is zero-based) in the enumerated collection, which contains names of files that match criteria defined on the Collection page.
Click on OK to confirm your choices and execute the content of the container by highlighting it and using the already familiar "Execute task" item from the context sensitive menu. You should see the names of the files from the target folder, satisfying the filtering condition that you specified, being displayed one by one in the dialog boxes similar to the ones we have seen when experimenting with the For Loop container.
In our next article, we will look closer into the remaining types of Foreach Loop containers.