Sequence Container in SSIS

13
7993

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:-

  1. They can be huge helpful when developing and debugging SSIS packages.
  2. 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.
  3. Instead of setting property for each individual task, we group tasks together that require similar property settings.
  4. Providing scope for variables that a group of related tasks and containers use.
  5. 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 :-

database

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 :-

 Insert

For Insert_2 Execute SQL Task :-

For Insert_3 Execute SQL Task :-

For Insert_4 Execute SQL Task :-

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”.

Transaction_property

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.

error

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 will execute our container. So, let’s make it work.

executed

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.

inserted_data

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. For more information, I would like you to go through this helpful article – Different ways of using Sequence Container.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.2/10 (58 votes cast)
VN:F [1.9.22_1171]
Rating: +47 (from 47 votes)
Sequence Container in SSIS, 9.2 out of 10 based on 58 ratings
  • Pingback: Containers in SSIS | pHpRing()

  • Bani

    Hi Chander,

    Thanks for posting articles on Sequence container in SSIS and similar MSBI posts. I am not MSBI savvy ….As per my requirement i need to learn a the basics but after reading this post i believe SSIS must be an interesting ETL tool to learn ..
    Keep posting more such stufz!

    • venkat

      Hi Chander, thanks to so many articles given to used really, but how to make the real time scenerious of SCD (Slowly Changing Dimension) to uploaded the descriptions and ssis logfiles how to change etc…..

  • Pingback: Container in SSIS()

  • sanjiv

    helpful

  • Aanchal Kapoor

    Beautifully explained. Thanks for providing SSIS tutorials

  • swapnil

    Really understable for beginners also. i can suggest this website for beginners.

  • bunty

    I have requirement for downloading files from sharepoint folder and not only an single file there will be around 10 files.on the daily basis i want to download.

  • Swati Patil

    Wonderful explanation. Thank you very much.

  • umar

    Nice explanation can you provide some more articles on sequence container

  • soubhagya

    nicely explained

  • Pingback: Ssis Deployment |()

  • Pingback: Ssis Etl Tool |()