h1

Using Sequence Containers in SSIS

March 21, 2012

Not every package requires the use of a Sequence container, and not everyone likes to use them. For me personally they can be a huge help when developing and debugging SSIS packages.  Some of the most common uses for Sequence Containers are:

  • Group and control the execution order of a package.  For example loading a data warehouse, place all of the dimension load tasks inside the sequence container and allow them to run in parallel, then connect the sequence container to the fact table load to ensure that all the dimensions finish loading before the fact table loads.

image

  • Grouping related tasks in a package to give a clearer “High Level” understanding of what a complex package is doing.

  • Easier debugging by allowing you to disable groups of tasks to focus package debugging on one subset of the package control flow.

image

  • Grouping tasks that require similar property settings without having to set each individual task property.  Similarly it allows you to set  properties at the container level and not at the package level.

  • Providing scope for variables that a group of related tasks and containers use.

  • Create a transaction around all the components inside the container.  Meaning if one task inside the container fails all the work that was completed successfully prior to that task failing is rolled back and its like it never happened.

Its this last point that I would like to look at in more detail.  To be quite honest I haven’t always been aware that you could create a transaction using a sequence container, and it would have made my life a bit easier. If you head over to MSDN you can find these articles covering transactions (1, 2, 3). All Microsoft Integration Services container types allow you to configure them for use in transaction with one of three settings.  MSDN describes them:

  • Required indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction. For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.

  • Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.

  • NotSupported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.

This means that at the Package level, at the Sequence, For Loop and For Each Loop Container level as well as most if not all (sorry I haven’t checked them all, but you can if you want to) Control Flow containers you can configure them to use or ignore a transaction. Just by changing the TransactionOption property. By default the property is set to Supported, which I find rather convenient, because then I only have to set it at the Parent container level and every child will automatically pick up the transaction. I like it when I don’t have to change the property of a handful of tasks.

So lets create a few examples.

Lets start simple.  I’ve seen this example done by a few people, here and here, and if its good enough for them, its good enough for me. Lets create a package that creates a table and inserts a bunch of rows using multiple Execute SQL tasks.  Here are the SQL statements for the 5 tasks:

CREATE TABLE [dbo].[TransactonTest](
[PartNumber] [int] NULL CONSTRAINT uq_TransactionTest_PartNumber UNIQUE,
[PartDescription] [Varchar] (200) null


) ON [PRIMARY]





INSERT INTO [dbo].[TransactonTest]
([PartNumber]
,[PartDescription])
VALUES
(1
,'Spacely Sprokets')

INSERT INTO [dbo].[TransactonTest]
([PartNumber]
,[PartDescription])
VALUES
(2
,'Cogswells Cosmic Cogs')


INSERT INTO [dbo].[TransactonTest]
([PartNumber]
,[PartDescription])
VALUES
(3
,'Thing 1')


INSERT INTO [dbo].[TransactonTest]
([PartNumber]
,[PartDescription])
VALUES
(3
,'Thing 2')

image

As you can see I’ve added a Sequence Container and put the Execute SQL tasks inside the container.  I’ve also set the TransactionOption for the Container to Required.  Now when I run my package the final insert should fail. 

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.

So to enable the service on my laptop I went tp ControlPanel->AdministrativeTools->Component Services->Computers->My Computer->Properties and started the Distributed Transaction Coordinator, and that solved my problem. However if you are using this in your work environment be aware that there are some hurdles to jump through regarding firewalls  

So now back to the package.

image

As expected the 4th insert fails and the container fails.  If we look at my server you’ll see that the table didn’t get created.

image

See I told you no table named TransactionTest.  But if we go ahead and change the 4th insert to work…

image

image

image

Well there you go, it really does work.  Pretty cool little tidbit to have in your back pocket.  Now don’t go thinking that this will solve all of your problems, some people (SSIS Junkie, Matt Masson) and  have encountered issues with using transactions in some instances, so make sure to test this out thoroughly.

This will work on package designs for loading a data warehouse star schema using Execute Package Tasks as well. 

image

As long as the tasks is set to supported, and the items inside the child package are set to supported as well the will inherit the transaction created by the parent container.

I hope this opened up your eyes to the fact that you can use Sequence containers to do a number of useful things and that incorporating transactions inside your SSIS packages is possible and not to difficult to implement.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: