Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Containers

DTS 2000 packages contained a hierarchical view of a package known as the DTS object model. The replacement for DTS, SQL Server Integration Services, has a similar concept of a package being represented as a hierarchy but it is not as rigid as before.

In DTS 2000 a new package already contained a hierarchy which included a task collection, connections collection, steps collection plus various other variables/objects. The collections were empty at the start of course, but they were there.

SSIS isn't as rigid as this. Conceptually a brand new package in SSIS has a containers collection, and little else. [In actual fact this isn't true, architecturally, but for now it helps to think of it this way] Containers can contain other containers and so on and so forth...thus building up a hierarchy of containers. The package itself is a container and exists at the root of this container hierachy. A brand new package will contain 1 and only 1 container, the package itself.

So what is a container? A container is either an atomic unit of work or a collection of other containers. There are different kinds of containers in SSIS. The definitive list is:

  • Package
  • For Loop
  • Foreach Loop
  • Sequence
  • TaskHost

UPDATE: Additionally an event handler is also a container although it won't appear in the normal control-flow (thanks to Kirk for pointing this out)!

Containers are fundamental to the operation of transactions, checkpoints and event handlers. Each container has some common properties that affect the usage of these features:

  • Disable
  • DisableEventHandlers
  • FailPackageOnFailure
  • FailParentOnError
  • IsolationLevel
  • MaximumErrorCount
  • TransactionOption

Understanding these properties and what they do will help a great deal in the building of SSIS packages, particularly with regard to transactions and checkpoints. [Note that this is not a definitive list of container properties.]

Each time you add an item to the control-flow of an SSIS package you are adding a container. This may be obvious to you (e.g. When you add a sequence container) or less obvious when it happens implicitly (e.g. When you add a data flow). Each atomic unit of work exists in an "invisible" container called the TaskHost container. Every leaf of the the container hierarchy will invariably be a TaskHost container - if it isn't then its more likely that the person building the package has added something like a sequence container or a Foreach container that doesn't contain any tasks...in other words an object that doesn't actually do anything.

SSIS Books Online alludes to the importance of the concept of containers but doesn't explicitly state clearly enough how relevant they are. This post is an attempt to clarify how fundamental containers and the container hierarchy actually are to SSIS, in my opinion it is a concept any SSIS beginner needs to become familiar with as soon as they start using the product.

Hopefully this post has served to educate rather than muddy the waters which is what my waffling explanations usually tend to do. I'd appreciate feedback letting me know whether you agree with what I've written or whether I should be shot down in flames for misleading people.

Feel free to fire any questions at me as well!

- Jamie

 

Published Monday, December 13, 2004 3:05 PM by jamie.thomson

Comments

 

jamie.thomson said:

Missed one container, that is the Event Handler.

Great job with the container discussion. It truly is an important concept in SSIS.
December 13, 2004 4:54 PM
 

jamie.thomson said:

Thanks for that Kirk, I've updated the post accordingly!
December 13, 2004 5:06 PM
 

TrackBack said:

Some other blogs...
December 13, 2004 5:25 PM
 

TrackBack said:

December 14, 2004 1:13 PM
 

TrackBack said:

December 14, 2004 1:47 PM
 

TrackBack said:

March 17, 2005 9:17 PM
 

TrackBack said:

March 17, 2005 9:18 PM
 

TrackBack said:

April 20, 2005 9:50 PM
 

TrackBack said:

June 8, 2005 10:02 AM
 

The Industry Insiders said:



SSIS: Custom Logging Using Event Handlers
SQL Server Integration Services (SSIS) contains some...
June 30, 2005 5:55 PM
 

The Industry Insiders said:



SSIS: Custom Logging Using Event Handlers
SQL Server Integration Services (SSIS) contains some...
June 30, 2005 5:55 PM
 

The Industry Insiders said:

SSIS: Custom Logging Using Event Handlers
SQL Server Integration Services (SSIS) contains some really...
June 30, 2005 6:00 PM
 

Jamie Thomson's Blog said:

In the dim and distant past I wrote a blog entry that attempted to explain the concept of containers...
July 13, 2005 10:59 PM
 

Jamie Thomson's Blog said:

In the dim and distant past I wrote a blog entry that attempted to explain the concept of containers...
July 13, 2005 11:07 PM
 

Jamie Thomson's Blog said:

In the dim and distant past I wrote a blog entry that attempted to explain the concept of containers...
July 14, 2005 7:03 AM
 

Jamie Thomson - Life, the universe and SSIS! said:

In SQL Server Integration Services (SSIS) variables have scope (this was not the case in the forerunner...
September 1, 2005 10:14 AM
 

Jamie Thomson - Life, the universe and SSIS! said:

Some time ago when SSIS was still in the beta stage I started to build my own custom logging procedures...
January 16, 2006 9:26 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

Earlier today I wrote a short blog post explaining a problem with the System::ContainerStartTime variable which...
January 18, 2006 8:26 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

UPDATE: There are a couple of small issues with the code below that you should read about here: http://blogs.conchango.com/jamiethomson/archive/2006/01/18/2654.aspx...
January 18, 2006 9:23 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

UPDATE: There are a couple of small issues with the code below that you should read about here: http://blogs.conchango.com/jamiethomson/archive/2006/01/18/2654.aspx...
January 20, 2006 3:08 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

UPDATE: There are a couple of small issues with the code below that you should read about here: http://blogs.conchango.com/jamiethomson/archive/2006/01/18/2654.aspx...
January 20, 2006 3:08 PM
 

Jamie Thomson - SSIS Jibber Jabber said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
May 25, 2006 1:02 PM
 

SSIS Junkie said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices.

November 8, 2006 9:52 PM
 

SSIS Junkie said:

One of the least-known features in SSIS, certainly one of the least-used, is the OnVariableValueChanged

January 16, 2007 6:00 PM
 

SSIS Junkie said:

In SQL Server Integration Services (SSIS) variables have scope (this was not the case in the forerunner

January 16, 2007 6:15 PM
 

SSIS Junkie said:

SQL Server Integration Services (SSIS) contains some really useful logging procedures but as with most

January 16, 2007 6:17 PM
 

SSIS Junkie said:

WOOHOO! The ContainerStartTime system variable is a new feature in the latest CTP drop of SQL Server

January 16, 2007 6:18 PM
 

SSIS Junkie said:

What are checkpoints? SQL Server Integration Services (SSIS) contains a fantastic new feature called

January 24, 2007 1:58 AM
 

latina anal fingering said:

latina anal fingering

April 5, 2007 1:16 PM
 

SSIS Junkie said:

A question was raised on a beta newsgroup earlier today concerning how to use variables across different

April 26, 2007 8:35 PM
 

SSIS Junkie said:

I often find that one of the biggest obstacles to technology adoption is when people refer to the same

November 19, 2007 9:47 PM
 

SSIS Junkie said:

As SQL Server 2008 is practically now a done deal my thoughts have turned to what will come in future

April 15, 2008 11:18 PM
 

SSIS Junkie said:

In the dim and distant past I wrote a blog entry that attempted to explain the concept of containers

May 25, 2008 8:41 PM
 

SSIS Junkie said:

That’s what I twittered recently upon first opening up Release Candidate 0 of SQL Server 2008 . You may

June 7, 2008 7:17 PM
 

SSIS Junkie said:

In my blog entry Custom Logging Using Event Handlers from June 2005 I described an easy technique for

June 8, 2008 12:42 PM
 

spartan said:

hi jamie,

im using a for each loop file enumerator to load many excel files into database. im having ten tasks in the container. my problem is when any one task failed for a file, the container have to leave the remaining tasks for that file and move on to execute other files.

by setting  ForceExecutionResult to Success the container is executing all the tasks. but i dont want that. wat i need is if 3rd task fails means 4th task should not run for that particular file. but for the next file all the tasks should run continuously.

i think u understand my problem

please help me

with kindest regards,

kart

December 29, 2008 11:20 AM
 

jamie.thomson said:

Kart,

Try setting FailParentOnFailure=FALSE for all of the tasks.

-Jamie

January 1, 2009 4:21 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems