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:
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:
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:
Double-click on Sequence container and rename it to Sunday as shown below:
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:
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:
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:
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.
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:
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:
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:
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:
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 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:
- Result set: Single row
- Connection: Specify SQL instance connection details
- SQLStatement: Copy-paste the following T-SQL in this editor
|
1 |
SELECT DATENAME(dw, GETDATE()) AS dayofweek; |
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.
Navigate to the Result set and map the query output with the SSIS variable:
Click OK and join the precedence constraint from SQL task to Sunday Sequence container in SSIS:
Double-click on this precedence constraint and change the property as follows:
- Evaluation operation: Expression and constraint
- Value: Success
- Expression:
1@[User::Day]=="Sunday"
You can click on the test to verify the expression. It gives the following message for successful validation:
Click OK, and you can see the following configuration for precedence constraint with Sunday sequence container in SSIS:
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:
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:
Here we go. In the following screenshot, we can see that only the Wednesday Sequence container in SSIS is executed:
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:
It disables the Sequence container. It also greyed out the task inside:
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:
We can collapse or expand a Sequence container in SSIS package with a click on the arrow as shown below:
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
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023