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