SSIS 2008 Control Task - 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
• Creating a transaction across a series of data - related tasks, but not on the entire package
• Creating event handlers on a single container, wherein you could send an email if anything inside one container fails and perhaps page if anything else fails

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 below gives you an example of a Sequence Container. The container is a Sequence

Container where two tasks must execute successfully before the task called “ Run Script 3 ” will be

executed. If you were to click the up - pointing arrows, the tasks inside the container will minimize.

A container can be considered to be a miniature package. Inside the container, all task names must be

unique, just like from within a package where there are no containers. You also cannot connect a task in

one container to anything outside of the container. If you try to do this, you will receive the following

error:

                             Cannot create connector.

                             Cannot connect executables from different containers.

Containers such as the Sequence Container can also be embedded in each other. As a best practice each

of your SSIS packages should contain a series of containers to help organize the package and to make it

easy to disable subject areas quickly. Each set of tables that you must load probably fits into a subject

area, such as Accounting or HR. Each of these loads should be placed in its own Sequence Container.

Additionally, you may want to create a Sequence Container for the preparation and cleanup stages of

your package.

Groups

Groups are very similar to Sequence Containers with some very important differences. Groups are simply a collection of tasks and are not true containers. A key difference is that properties cannot be delegated through a container. Because of this, they don ’ t have precedence constraints originating from them (only from the tasks). You also can ’ t disable the entire group as in a Sequence Container. What they are good for is a quick compartmentalization of tasks for aesthetics.

To create a group, you highlight the tasks that you wish to place in the group, right - click, and select Group. To ungroup the tasks, right - click the group and select Ungroup. To add additional tasks into the group, simply drag the task into the group.

The same type of logic you saw in the Sequence Container earlier (Above figure ) can be seen in Figure Below As you can see, the precedence constraint is originating from the task called “ Run Script 2 ” to “ Run

Script 3 ” .

For Loop Container

The For Loop Container is a method to create looping in your package, similar to how you would loop in

nearly any programming language. 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 Below, you can see that the Script Task called “ Wait for File to Arrive ” is

continuously looped through until a condition is evaluated as false. Once the loop is broken, the Script

Task is executed. Another real - world example would be to use a Message Queue Task inside the loop to

continuously loop until a message arrives in the queue. Such a configuration would allow for scaling out

your SSIS environment.

Let ’ s try a simple example to demonstrate the functionality of the For Loop Container, where we ’ ll use

the container to loop over a series of tasks five times. Although this example is pretty rudimentary, you

can plug whatever task you want in place of the Script Task.

1. Create a new SSIS project , 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 toopen 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 ) and a default value of 0.
3. Drag the For Loop Container to the Control Flow and double - click 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 Below ) and click OK.

  4. Next, drag a Script Task into the For Loop Container and double - click 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 Below ) to Counter and select Microsoft

      Visual Basic 2008. Finally, click Edit Script to open the Visual Studio designer. 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 2008 design environment will open. Double -

            Click ScriptMain.vb to open the script and 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()

                     

                   Add your code here                   

                   MsgBox(Dts.Variables(“Counter”).Value)

                   Dts.TaskResult = ScriptResults.Success

                   End Sub

     7. Save and 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 Below . You should see five pop -

        up boxes starting at iteration 0 and proceeding through iteration 4. Only one popup will appear

        at any given point. 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.

Foreach Loop Container

The Foreach Loop Container 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 variable, which could later be used by tasks or connections inside or outside the container. 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 behavior of the editor varies widely based on what you set for this option:

• Foreach File Enumerator: Performs an action for each file in a directory with a given file extension .
• Foreach Item Enumerator: Loops through a list of items that are set manually in the container .
• Foreach ADO Enumerator: Loops through a list of tables or rows in a table from an ADO record set .
• Foreach ADO.NET Schema Rowset Enumerator: Loops through an ADO.NET schema .
• Foreach From Variable Enumerator: Loops through an SSIS variable .
• Foreach Nodelist Enumerator: Loops through a node list in an XML document .
• Foreach SMO Enumerator: Enumerates a list of SQL Management Objects (SMO) .

Foreach File Enumerator Example

In this example we ’ ll use the most common type of enumerator, the Foreach File enumerator, to loop through a list of files and simulate some type of action that has occurred inside the container. This example has been simplified in an effort to just show the core functionality, but if you ’ d like a much more detailed example.

 To start, create a new package and string variable called sFileName with a default value of the word default . This variable will hold the name of the file that SSIS is working on during each iteration of the loop. Create the variable by right - clicking in the Package Designer area of the Control Flow tab and selecting Variables. Then, click the Add New Variable option, changing the data type to a String. Next, drag over a Foreach Loop Container onto the Control Flow and double - click on the container to configure it, as shown in Figure Below . Set the Enumerator option to Foreach File Enumerator. Then, set the Folder property to C:\Projects\Pro SSIS 2008\Temp and leave the default Files property of *.* .

In the Variable Mappings page in the container, select the earlier created variable from the Variable drop -

down box and then accept the default of 0 for the index, as shown in Figure Below . Click OK to save the

settings and to get back to the Control Flow tab in the Package Designer.

Drag over a new File System Task into the container ’ s box. Double - click the new task to configure it. After setting the operation to Move File, you ’ ll see the screen ’ s properties change to resemble Figure Below (once this example ’ s step is complete). Select < New Connection > for the DestinationConnection

property. When the Connection Manager dialog box opens, select Existing Folder and type C:\ Projects\Pro SSIS 2008\Temp\Archive for the directory. Lastly, set the IsSourcePathVariable property to True and set the SourceVariable to User::sFileName .

You ’ re now ready to execute the package. Place any set of files you wish into the C:\Projects\

Pro SSIS 2008 folder and execute the package. During execution you ’ ll see each file picked up and

moved in Windows Explorer, and in the package you ’ ll see what resembles Figure 4 - 10 . If you had set

the OverwriteDestination property to True in the File System Task, the file would have been

overwritten if there was a conflict in duplicate file names.