Chapter 4: Containers and Data Flow

In the last chapter you were introduced to the Package Designer. In this chapter, you'll continue along those lines with an exploration of the Control Flow tab and spend most of your time in the Data Flow task. The Data Flow task is where the bulk of your data heavy lifting will occur. This chapter walks you through how each transformation in the Data Flow task can help you move and clean your data.

Containers

Containers are objects that help SSIS provide structure to one or more tasks. They can help you loop through a set of tasks until a criterion has been met or can help you group a set of tasks logically. Containers can also be nested, containing other containers. Containers are set in the Control Flow tab in the Package Designer. There are four types of containers in the Control Flow tab: Task Host, Sequence, For Loop, and Foreach containers.

Task Host Containers

The task host container is the default container that single tasks fall into. You'll notice that this type of container is not in the Toolbox in Visual Studio. In fact, even if you don't specify a container for a task, it will be placed in a task host container. The SSIS architecture extends variables and event handlers to the task through the task host container.

Sequence Containers

Sequence containers handle the flow of a subset of a package and can help you divide a package into smaller, more-manageable pieces. Some nice applications that you can use sequence containers for include the following:

• Grouping tasks so that you can disable a part of the package that's no longer needed

• Narrowing the scope of the variable to a container

• Managing the properties of multiple tasks in one step by setting the properties of the container

• Using one method to ensure that multiple tasks have to execute successfully before the next task executes

Sequence containers show up like any other task in your Control Flow tab. Once you drag and drop any container from your Toolbox onto the design pane, you just have to drag the tasks you'd like to use into the container. Figure 4-1 gives you an example of two containers. The left container is a Sequence container where two tasks must execute successfully before the Foreach Loop container will be executed.

Figure 4-1

For Loop Container

The For Loop container is a method to create looping in your package similar to how you would in nearly any programming language, using the for looping. In this looping style, SSIS optionally initializes an expression and continues to evaluate it until the expression evaluates to false.

In the example in Figure 4-2, you can see that the Message Queue task is continuously looped through until a condition is evaluated as false. Once the loop is broken, the Script task is executed. This is useful when you'd like to try retrieving a message from a Microsoft Message Queue five times. By default, the Message Queue task will execute indefinitely until it retrieves the queue. With this method, you could set the time-out of the Message Queue task to 30 seconds and let it retry four times until it either retrieves the message or fails. In a more advanced example, you could build logic into the precedence constraints to state that if the variable was retrieved from the queue, you want to execute the Script task, and if not, to send a message.

Figure 4-2

To set the properties of a For Loop container, double-click on the container box and the For Loop Editor will open, as shown in Figure 4-3. The InitExpression option, which is optional in the For Loop page, initializes the loop. The initial expression can also be set elsewhere when the expression is declared. The EvalExpression is the expression that will be evaluated each loop. Once this expression returns false, the loop will be stopped. The last parameter, the AssignExpression option, optionally can change the expression each time the loop repeats.

Figure 4-3

You can now try out some of the containers that have just been demonstrated. In this example, you'll see how to use a For Loop container to iterate through a script five times. While this example is pretty rudimentary, you can plug whatever task you want in place of the Script task.

1. Create a new SSIS project called Chapter4, and change the name of the default package to ForLoop.dtsx.

2. Open the ForLoop.dtsx package, create a new variable, and call it Counter. You may have to open the Variable window if it isn't already open. To do this, right-click in the design pane and select Variables. Once the window is open, click the Add Variable button. Accept all the defaults for the variable (int32).

3. Drag the For Loop container to the control flow and double-click on it to open the editor. Set the InitExpression option to @Counter = 0. This will initialize the loop by setting the Counter variable to 0. Next, in the EvalExpression option, type @Counter < 5 and @Counter = @Counter + 1 for the AssignExpression. This means that the loop will iterate as long as the Counter variable is less than 5, and each time it loops, 1 will be added to the variable. The last step to configure the For Loop page is to type for the name option "Iterate through a Script" (shown in Figure 4-3) and click OK.

4. Next, drag a Script task into the For Loop container and double-click on the task to edit it. In the General tab, name the task "Pop Up the Iteration."

5. In the Script tab, set the ReadOnlyVariables (Figure 4-4) to Counter and click Design Script. By typing Counter for that option, you're going to pass in the Counter parameter to be used by the Script task.

6. When you click Design Script, the Visual Studio 2005 design environment will open. Replace the Main() subroutine with the following code. This code will read the variable and pop up a message box that tells you what the value of the Counter variable is.

    Public Sub Main()     Dim variables As Variables     If Dts.Variables.Contains("Counter") = True Then         Dts.VariableDispenser.LockOneForRead("Counter", variables)     End If     MsgBox("You are in iteration: " & CStr(variables("Counter").Value))     Dts.TaskResult = Dts.Results.Success End Sub 

7. Exit the Visual Studio design environment and click OK to exit the Script task. When you execute the package, you should see results similar to Figure 4-5. You should see five pop-up boxes starting at iteration 0 and proceeding through iteration 4. You'll see the Script task go green and then back to yellow as it transitions between each iteration of the loop. After the loop is complete, the For Loop container and the Script task will both be green.

Figure 4-4

Figure 4-5

Foreach Loop Container

The Foreach Loop is a powerful looping mechanism that allows you to loop through a collection of objects. As you loop through the collection, the container will assign the value from the collection to a task or connection inside the container as shown below. You can also map the value to a variable. The type of objects that you will loop through can vary based on the enumerator you set in the editor in the Collection page. The editor varies widely based on what you set for this option. You can set this to one of the following options:

• For Each File Enumerator: Performs an action for each file in a directory with a given file extension

• For Each Item Enumerator: Loops through a list of items that are set manually in the container

• For Each ADO Enumerator: Loops through a list of tables or rows in a table from an ADO record set

• For Each ADO.NET Schema Rowset Enumerator: Loops through an ADO.NET schema

• For Each From Variable Enumerator: Loops through a SSIS variable

• For Each Nodelist Enumerator: Loops through a node list in an XML document

• For Each SMO Enumerator: Enumerates a list of SQL Management Objects (SMO)

You will use an example similar to your For Loop for the Foreach Loop. In this example, you'll iterate through a collection of files in a directory and pop up a message for each file in the directory. Again, in reality you'd have the script perform some type of more meaningful action, but you can plug in that action easily later. In Chapter 5, you will dive into a detailed example using this container.

1. Add a new package to your Chapter4 project, and rename it ForeachLoop.dtsx.

2. Drag a Foreach Loop container into the control flow and double-click it to open the editor. In the General page, name the container "Iterate through Files" and type whatever description you'd like.

3. In the Collection page, keep the default Enumerator "Foreach File Enumerator." For the Folder option, select a folder that has only a few files. Your files will not be harmed or moved in any way, but a message will pop up for each file. For the Files option, keep the default *.* value as shown in Figure 4-6.

4. Go to the Variable Mapping page. In the Variable column drop-down box, select <New variable…>, which will open the Add Variable dialog box.

   Name the variable FileName, and do not assign any value to it. Click OK to go back to the Variable Mapping page. You'll now see that the variable name has been transposed into the Variable column and that a 0 is in the Index column. The Foreach File Enumerator has only one column in its index, so this is fine. Click OK to exit the editor.

5. Drag a Script task onto the design pane and double-click it to open the editor. Name the task "Read Files" and type any description.

6. Go to the Script page and type the value of FileName for the ReadOnlyVariable option. Then, click Design Script to open the Visual Studio environment. Replace the Main() subroutine with the following code:

    Public Sub Main()     Dim variables As Variables     If Dts.Variables.Contains("FileName") = True Then         Dts.VariableDispenser.LockOneForRead("FileName", variables)     End If     MsgBox("Found the file: " & CStr(variables("FileName").Value))     Dts.TaskResult = Dts.Results.Success End Sub 

7. Exit the script designer and click OK to exit back into the control flow. Execute the package and you should see results similar to Figure 4-7 (depending on what directory you pointed to).

Figure 4-6

Figure 4-7