In the dim and distant past I wrote a blog entry that attempted to explain the concept of containers in an SSIS package. Following a post from Paul Pisarek this evening on the SSIS forum I thought it might be beneficial to expand on the concept a little.
Hopefully after reading the blog entry linked to above you are familiar with how a package is, in essence, a hierarchy of containers with the package container itself at the root of that hierarchy. You may think that if you have an SSIS solution that contains a master package and multiple other packages being called using the Execute Package Task then your solution consists of multiple seperate container hierarchies with no relationship to each other. Well actually...this is not the case!!!
A package container is the root of that package's container hierarchy yes, but put multiple container hierarchies together and what do you get? Answer: One single container hierarchy spanning multiple packages - effectively comprising many smaller container hierarchies in each package. The package container of each package executed via the Execute Package Task is a child of the TaskHost container of the Execute Package Task that executed it.
Why is this important? Well, for a number of reasons actually.
- Events raised in a package executed via the Execute Package Task will propogate up the container hierarchy and can be handled by an eventhandler in a different package.
- Therefore an event can be handled by more than one package (i.e. Not one or the other).
- Setting DisableEventHandlers=FALSE in the package that raises an event does NOT ensure that an event will not get handled by an eventhandler - as Paul found out to his cost :)
- Containers are integral to how transactions operate in SSIS. The fact that a container hierarchy can span multiple packages means that transactions can do so as well. In other words we can commit or rollback multiple operations over multiple RDBMSs that are executed from multiple packages. That's fantastically powerful stuff! You can read a bit more about this here.
-Jamie