How to use Sequence Container in SSIS

Description: -

In this article we are going to see how to use a Sequential Task container. This container is used in areas where the process needs to follow certain tasks sequentially. Sequential Task groups the tasks into multiple control flows and executes the process sequentially. We can use this task widely based on our requirements; such as disabling a sequence when it should not process or using it when managing multiple tasks in one location. We can easily disable a set of tasks of sequential tasks by disabling a sequential task alone which can easily be done. If there are many tasks in our package we can group them based on their sequence and used to collapse and expand them and also to enable and disable them easily.

The Sequence container defines a control flow that is a subset of the package control flow. 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.

The Sequence container can include multiple tasks in addition to other containers. Adding tasks and containers to a Sequence container is similar to adding them to a package, except you drag the tasks and containers to the Sequence container instead of to the package container. If the Sequence container includes more than one task or container, you can connect them using precedence constraints just as you do in a package.

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 e-mail if anything inside one container fails and perhaps page if anything else fails

In this example I will use my previous task to run the sequence container and will check how it works. Here I have used below task to execute sequence container.

1) How to use For Loop Container in SSIS.

2) How to use Row Count Transformation in SSIS.

3) How to use OLEDB Command Transformation in SSIS.

Here is my package execution flow please check the below image.

Let’s check again what I have done in all the created task.

For Loop Container Task

In this task we have used For Loop Container and Data flow task, in the Data flow task we have used OLE DB Source control and Flat file destination to get data from the source control and store it into the flat file destination.

We have created Counter variable to run multiple times package that’s why we have used for loop. So after execution we can get multiple times same records in the flat file destination. Please check below image of the For Loop container task.

Row Count Transformation task

In this task we have used OLE DB Source control to get the data from the database using the Sql Command then after we have conditionally split the data for that we have created 2 (Two) variables ‘INR_Cur’ and ‘USD_Cur’. Using Row Count tools we have counted the rows and store it into the different flat file destination. Please check below image to understand the Row Count Transformation Task.

OLE DB Command Transformation Task

In this task we have used Flat file source as source tools for which records we want to delete from the data base table using the Sql Command. Using the OLE DB Command we have delete the records from the table which is in the flat file source. Please check the below image to understand the logic of the OLE DB Command Transformation task.

Here in the Sequence container we will create 3 (Three) different sequence container and drag and drop the created task into each sequence container like below image.