Sequence Container in SSIS 2008

The Sequence Container defines a control flow that is a subset of the control flow in a package. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow. There are some benefits of using a Sequence container which are mentioned below:

• Provides the facility of disabling groups of tasks to focus debugging on one subset of the package control flow.
• Managing multiple tasks in one location by setting properties on a Sequence Container instead of setting properties on the individual tasks.
• Provides scope for variables that a group of related tasks and containers use.

If a package has many tasks then it is easier to group the tasks in Sequence Containers and you can collapse and expand Sequence Containers.
Note: You can also create task groups which collapse and expand using the Group box this is a design-time feature that has no properties or run-time behavior.

Now I'll explain you how to use sequence Container in a package.

1. To begin, right click on SSIS Packages folder in Solution Explorer and click New SSIS Package. Rename it with SequenceContainer.dtsx as shown below:





2. Add a package variable DayOfWeek





3. Drag and drop Script Task from toolbox. This task will be used to initialize DayOfWeek variable with current day of week. Rename Task name as Script Task - Set DayOfWeek.


4. Double click on Script Task to open Script Task Editor. Enter User::DayOfWeek in ReadWriteVariables property.





5. Click on Edit Script... and write below code in Main() function of ScriptMain.cs:





6. Drag and Drop 7 Sequence Container tasks from Toolbox and rename the task on Week Days e.g. "SC - Sunday", "SC - Monday" etc. I am not placing any controls flow items inside Sequence Container to narrow down the example and focus more on how to use sequence container.


7. Connect all these Sequence Containers to Script Task. Now double click on green arrow to open Precedence Constraint Editor. Select Evaluation operator as Expression and Constraint and Expression as @DayOfWeek == "Sunday". Click OK to close and save changes. The Expression for remaining task will be differ as per week day e.g. @DayOfWeek == "Monday" for Monday and so on.





8. Save the package. Now right click on the package in Solution Explorer and execute the package.





9. This package will execute only one sequence container as per expression set for the precedence constraints.




This is just an example of how we can use Sequence Container. But we can use this in many ways which depends on the requirement.

Replacing a Cursor With a SSIS Package

This can be done several ways depending on the situation. In this situation there is a number on each row that determines the number of times a row needs to be written to the destination.

The source table & script looks like this:--->

The Number of Nights column tells us how many times this row needs to be inserted into the destination table. So the Destination should look like the following image after the load is complete. Notice the number of nights matches the number of times the row appears on the destination table.

This can be performed by using a cursor to loop through each row, but this is very slow. If you needed to perform this for millions of rows it would be a very long process. The power of SSIS is in the batch loads it performs in data flows. You can perform this using a small SSIS package. Here is an image of the package Control Flow you can create to perform this kind of cursor work.

This SSIS package will have two variables, intCounter and intNumOfNights. The counter variable will increment during the loop. The number of nights variable will hold the maximum number of nights from the source table.

The first task in the package is an Execute SQL Task. It retrieves the maximum number of nights and saves it in the number of nights variable. This will control the number of times the loop runs.

The query in the Execute SQL Task is:

The result set is single row and intNumofNights is mapped under result set.

The For Loop Container will loop from 1 to the max number of nights. The image below shows how this is set up. This is assuming the lowest number of nights will be 1.

The only thing left is the Data Flow. The source will be an OLEDB source with the following SQL query.

The question mark is a parameter and is mapped to the intCounter variable. This will only select rows that have the number of nights greater than or equal to the counter.

The destination is an OLEDB Destination. No special setup needed for this task, just map the source columns to the proper destination columns.

This package will give you the results in the first two table images. The parameter in the Data Flow source prevents it from loading a row too many times. The SSIS package will perform much faster than the SQL cursor because the cursor is row by row and the SSIS package performs the data flow in batch.