In my blog entry Custom Logging Using Event Handlers from June 2005 I described an easy technique for using SSIS eventhandlers to get more useful logging information from your packages than using the out-of-the-box logging providers and up to now it has proved to be one of my most popular blog entries. With the introduction of the @[System::SourceParentGUID] variable in SQL Server 2008 (which I gave an overview on yesterday in my blog entry One small step for the SSIS team, one giant leap for SSIS developers) we now have the ability to record much richer information about the context in which a task executes.
I have built a package that demonstrates this capability. Here is a screenshot of the control-flow of that package captured during mid-execution:
Some things to note about this package:
- There are 5 containers (i.e. The package container, 2 sequence containers and 2 taskhost containers)
- “DFT1” & “DFT 2” have the same level in the container hierarchy. In other words, they have the same number of ancestral containers
If we make use of @[System::SourceParentGUID] we can now derive logging information about each of those 5 containers that we previously haven’t been able to:
- ContainerStack – A delimited list of all of the ancestral containers of a container
- StackLevel – Effectively the number of ancestral containers that a container has
Let’s take a look at how that information materialises in our log table:
As has been highlighted we have captured the [ContainerStack] and [StackLevel] in our log table. In addition we have used [StackLevel] to indent values in the [SourceName] field. All of this information aids to provide context as to how a container came to be executed.
If you would like to try this package out for yourself then download it from here: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20080608/Pkg%20SQLParentDemo.zip
There is no setup required other than pointing it whatever SQL Server instance you want it to log into for which you will need to edit the ServerName property of the connection manager. Alternatively you can just execute the package from the command-line using the following:
- dtexec /f "Pkg SQLParentDemo.dtsx" /SET \Package.Connections[sql].Properties[ServerName];localhost
and replace “localhost” with the name of the SQL Server instance that you want to log to (N.B. if you are logging to localhost then you don’t need to change anything – the package is already setup to log to localhost).
When you execute the package it will attempt to create a database called [ParentLoggingDemo] so ensure that you have suitable permission to create a database. A final caveat is that the techniques demonstrated in this package are for demonstration purposes only and do not make any allowances for errors during execution so if you leverage these techniques in your own packages then you should allow for and test for errors accordingly.
Comments are welcome.
-Jamie