Sequence Container in SSIS

Description – Sequence container group related tasks in a package to show what the complex package is doing in a clear and simple way. The task of Sequence container is to have multiple separate control flows group together in a SSIS package. Each container will contain one or more tasks and will run within the control flow of overall package. Its not at all compulsory that every package should use Sequence container but there are some benefits of using this container. Some of them are described below:-

Benefits:-

They can be huge helpful when developing and debugging SSIS packages.
If a package has many tasks then it is easier to group the tasks in Sequence Containers and you can collapse and expand this container for usability.
Instead of setting property for each individual task, we group tasks together that require similar property settings.
Providing scope for variables that a group of related tasks and containers use.
If one task fails to succeed inside the container then the process is aborted and all the tasks that were completed successfully get rolled back for that container. This depends on Transaction option property which can be Required, Supported and Not Supported according to your configuration ( By default it’s Supported). It means it creates a Transaction around the components inside Container.

So, now let’s learn how to Implement Sequence Container. Example Scenario:– We will create one table named Friends in SSMS and one package in BIDS. Then we will insert some rows into our table using Multiple Execute SQL Tasks contained in a Sequence container. STEPS TO FOLLOW :- STEP 1. Let’s create a table, say Friends with the fields name and location. Open SQL Server Management Studio (SSMS) and Click on New Query. Select your present working database (I am using Chander as my database) and write the following script in it :-

1

2

3

4

5

CREATE TABLE [dbo].[Friends]

(

[Name] [Varchar] (200) not null,

[Location] [Varchar] (200) not null

)

STEP 2. Drag 4 different Execute SQL Tasks and Rename them to Insert_1, Insert_2, Insert_3 and Insert_4 respectively by double clicking the Execute SQL Task. Configure the SQL Task Editor as:-

Set Connection field to your working database (I am using Chander as my database).
Click on SQL statement field and write the following queries inside the respective Execute SQL Tasks. I am showing for Insert_1 Execute SQL Task. You can perform the same for other remaining Execute SQL Tasks.

For Insert_1 Execute SQL Task :-

1

2

INSERT INTO dbo.Friends  (Name, Location)

VALUES     ('Sanjay', 'South Africa')

For Insert_2 Execute SQL Task :-

1

2

INSERT INTO dbo.Friends  (Name, Location)

VALUES     ('Prince', 'England')

For Insert_3 Execute SQL Task :-

1

2

INSERT INTO dbo.Friends  (Name, Location)

VALUES     ('Amit', 'Australia')

For Insert_4 Execute SQL Task :-

1

2

INSERT INTO dbo.Friends  (Name, Location)

VALUES     ('Aakash')

NOTE :- We are inserting null in the location field for Insert_4 Execute SQL Task. The purpose is that this task will fail as we have mentioned not null for the Location field in our Friends Table (STEP 1). This will halt the execution of our package which we will see when we will execute it. STEP 3. Now, click on Sequence container and Drag it onto Control flow pane. Rename this container to “Create table and Insert all rows if succeed else Rollback”.

STEP 4. Now, put all the 4 Execute SQL Tasks into the Sequence container. By Defualt Transaction option property for the Container is Supported. Set this property to Required and execute the package.NOTE:– Before I could even get the package to fail for the reason I wanted to I got this error. Error:-The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B “The Transaction Manager is not available.”. The DTC transaction failed to start. This could occur because the MSDTC Service is not running. Sol:- So to solve this error and to enable the service on my Machine, Try these steps :- Control Panel ->Administrative Tools ->Component Services ->Computers ->My Computer ->Properties and start the Distributed Transaction Coordinator and if it is already running then stop it and start again.

STEP 5. As expected, 4th Insert fails and the container fails too. So, it will not create any table in our database. If we go to our Insert_4 Execute SQL Task and specify location for that field then it willexecute our container. So, let’s make it work.

1

2

INSERT INTO dbo.Friends  (Name, Location)

VALUES     ('Aakash', 'America')

STEP 6. It turns to Green, means it really does work. As long as the tasks is set to supported, and the items inside the child package are set to supported as well they will inherit the transaction created by the parent container. Let’s check our database to see the table created by the package.

With this we are finished with Implementing Sequence container in SSIS. There are many ways you can try, to play with this depending upon your requirement as well as your imagination. I hope this was not difficult to implement and made you realized how Sequence container can be used to perform number of things

Now lets learn each and every aspects of sequence container task

We are covering following points in this article

Why Sequence Container Task is Use ?

How Sequence Container Task is look like ?

Features of Sequence Container Task

How to use Sequence Container Task ?

What is alternative For Each Sequence Container Task ?

Demonstration of using Sequence Container Task ?

Various Properties For each Sequence Container Task ?

Reference link For Sequence Container Task

Why Sequence Container Task is Use

When we want perform some task sequentially or parallel we are going to make use of most of sequence container
which means though the name of the task is sequence container make point it can perform following two task:(A) Sequential execution of task (B)Parallel execution of task
The Sequence container groups together several tasks.
Use it to define a transaction boundary around a set of tasks so they all fail or succeed together. Or, use it simply to reduce the clutter on the design surface by hiding the detailed steps within the sequence.
We can also group control flow objects, and collapse or expand those groups. There’s no task for grouping.

Benefits of using a Sequence container:

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

How Sequence Container Task is look like ?

Its third in toolbox as shown :

Drag that out into your development plane

Features of Sequence Container Task

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
Sequence Container provides the facility of disabling groups of tasks to focus debugging on one subset of the package control flow
It have ability of 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.

How to use Sequence Container Task ? and

Demonstration of using Sequence Container Task ?

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