Rajendra Gupta
Complete package

Sequence Containers in SSIS packages

December 6, 2019 by

This article explores the Sequence container in SSIS package with examples.

Introduction

SSIS package control flow is useful for executing multiple tasks and design workflow for execution. A container in the control flow plays an essential role in workflow design. We can see the following containers in SSIS Toolbox:

SSIS Toolbox

For loop container

We can use this container for executing all inside tasks for a fixed number of executions. You can consider it equivalent to For loop in the programming language.

Foreach loop container

It works similar to For loop; however, we define a collection for determining the number of executions of For loop instead of a fixed number of executions. You can read more about in Using SSIS ForEach Loop containers to process files in Date Order article.

Sequence Container

The sequence container in SSIS is useful for grouping tasks together. We can split the control flow into multiple logical units using this. We will explore more on the Sequence container in this article.

Overview of the Sequence Container in SSIS

We can consider a Sequence container as a subset of an SSIS package. It acts as a single control point for the tasks defined inside a container.

We can summarize the benefits of a sequence container, as shown below:

  • We can define variables under the scope of tasks inside a sequence container
  • It follows a parent-child relationship with the underlying tasks. We can change the property of a sequence container (parent), and it is propagated to inside tasks (child)
  • It provides flexibility to manage the tasks in a container

Practical usage of Sequence container in SSIS

Let’s explore the sequence container practically.

Suppose you have a control flow for executing following SQL tasks daily:

SSIS package tasks

Currently, we have a similar procedure on each task that runs daily. It is also running on a fixed schedule by SQL Server Agent. Now, due to some business requirements, your development team created separate stored procedures for each day of the week.

We can create separate SSIS packages for each day and schedule SQL agent jobs. It increases the complexity and flexibility to manage the package:

  • Separate SSIS package for each day – 7 SSIS packages
  • Separate SQL Server Agent job for each SSIS package – 7 SQL Server jobs

Do we feel comfortable in doing this? No, right!

Sequence Container in SSIS package solves this problem for us. Let’s explore the solution.

Drag a sequence container from the SSIS toolbox to the control flow area. Currently, it does not have any tasks associated with it:

Drag a sequence container from SSIS toolbox to control flow area

Double-click on Sequence container and rename it to Sunday as shown below:

Empty sequence Container in SSIS

Now, drag and drop the SQL task 1 inside the Sunday container. You get the following error message that it cannot move a connected task to a new container. SQL task 1 connected with other tasks using precedence container:

Error message

We can remove the precedence constraints or select all SQL tasks together and move in the container.

Once we select all the tasks together, you can see bold outlines for each task:

Select all taks together

Now, move them together inside the Sunday sequence container in SSIS and resize the container so that we can fix another sequence container also on the screen. I have renamed the tasks and given them a shorter name:

Move tasks in a container

Make similar copies of the sequence container in the SSIS package for the rest of the week with appropriate scripts.

Note: We are not covering the configuration of individual tasks inside the container. You should have basic SSIS knowledge before using this article.

Now, my SSIS package looks like below with a Sequence container in SSIS for each day of the week.

SSIS package looks like below with sequence container

Currently, if we execute the SSIS package, it will execute each sequence container individually.

In the following screenshot, we can see that for each sequence container, task 1 fails and it marks container fails:

Error messages

It did not execute the task 3 because task 3 contains multiple precedences, and by default, all inputs to a task should be true.

Right-click on the precedence and modify it to Logical OR:

precedence editor

It changes the solid precedence lines to dotted lines. Fix the issue and execute the package and we can see each sequence contains runs inside task individually:

SSIS configuration

Now, we need to execute the Sequence container based on the day of the week. For this, right-click on the package and add a variable:

Variables

Click on Add variable and provide a name, data type for the variable. By default, the variable scope is at the package level. We will use this variable for the current day of the week:

Add Variable

Add a new execute SQL task and rename to find the day of the week:

Add a new execute SQL task and rename to find the day of the week.

Double-click on this task, and it opens the editor window. Make the following changes in this editor:

  1. Result set: Single row
  2. Connection: Specify SQL instance connection details
  3. SQLStatement: Copy-paste the following T-SQL in this editor

This query uses the DATENAME function and GETDATE function to find today’s day of the week. For example, it returns Wednesday for 27/11/2019.

SQL task editor

Navigate to the Result set and map the query output with the SSIS variable:

Configure result set

Click OK and join the precedence constraint from SQL task to Sunday Sequence container in SSIS:

Add precendence constraint

Double-click on this precedence constraint and change the property as follows:

  • Evaluation operation: Expression and constraint
  • Value: Success
  • Expression:

precedence constraint editor

You can click on the test to verify the expression. It gives the following message for successful validation:

expression validation

Click OK, and you can see the following configuration for precedence constraint with Sunday sequence container in SSIS:

precedence constraint with expression

Similarly, add the precedence constraint from SQL task to respective sequence container in SSIS. Make sure to change the expression for the particular day of the week. You can refer to the following table for expressions:

Monday

@[User::Day]==”Monday”

Tuesday

@[User::Day]==”Tuesday”

Wednesday

@[User::Day]==”Wednesday”

Thursday

@[User::Day]==”Thursday”

Friday

@[User::Day]==”Friday”

Saturday

@[User::Day]==”Saturday”

Now, my SSIS package configuration looks as per the following screenshot:

Complete package

The flow of this SSIS package will be:

  • First, it executes the SQL task Find the day of the week
  • It assigns the day of the week value to the SSIS variable
  • In the precedence constraint, we defined expression to check for the day of the week
  • It checks for the expression and executes the Sequence constraints, if the expression evaluates to true

For example, I am running this package on 27/11/2019 that is Wednesday. Let’s execute the SSIS package. It should execute a sequence container for Wednesday:

Calendar

Here we go. In the following screenshot, we can see that only the Wednesday Sequence container in SSIS is executed:

Execute package based on day of the week

An additional property of a sequence container in SSIS

We can disable a Sequence container as well to exclude from execution. Right-click on it and click on Disable:

disable a sequence container

It disables the Sequence container. It also greyed out the task inside:

disable a sequence container

We can design nested Sequence containers as well. In the following screenshot, we added a Sequence container inside the Sunday Sequence container. Once the task 2 is successful, it triggers the nested container execution:

Nested  sequence container

We can collapse or expand a Sequence container in SSIS package with a click on the arrow as shown below:

Expand or collapse container

We can configure sequence container property as well. Few useful properties are:

  • FailPackageOnFailure: It controls whether the package failure behavior in case of executable failure
  • MaximumErrorCount: It shows the maximum number of errors inside a sequence container in SSIS. If the numbers of errors are less than this parameter, a sequence container is marked successful even in case of failure. The default value is 1
  • Isolation level: By default, it supports isolation level Serializable
  • Disable: We can enable or disable a sequence container in an SSIS package using this property

Sequence container properties

Conclusion

In this article, we demonstrated the Sequence container in the SSIS package. It is useful in combining tasks and defining the package workflow. You should practice this container as per your requirement and use it.

Rajendra Gupta
5,785 Views