Which Package Was That?

One of my biggest complaints about the DTEXEC utility included with SSIS is that its output does not contain the name of the package being executed to produce the output. Obviously, if you're only running a single package this is not a problem, but many real world SSIS scenarios (including 99.99% of mine) involve the execution of master packages which in turn execute other packages by using the Execute Package task.

To make matters worse, if you use template packages (which is generally the case, because there is not much of a reusability story in SSIS) then you probably have the same set of tasks with the same names in each package. So when you get this error message output[1] from DTEXEC...

Error: 2008-03-03 19:44:42.21
   Code: 0xC0024104
   Source: Script Task - Set Package Variables 
   Description: The Execute method on the task returned error code 0x80004003 (Attempted to read or write protected memory. This is often an indication that other memory is corrupt.). The Execute method must succeed, and indicate the result using an "out" parameter.
End Error

...how are you supposed to figure out which package has failed?

The short answer is "with great difficulty."

But with a little basic work inside your packages (and you're probably doing something similar to this already, so this could well be work that you have already performed) the slightly-longer answer could be "really easily."

So what do you need to do to make this diagnostic task simple? You need to have implemented some sort of logging in your packages. All of my production packages have a logging pattern that looks something like this:

1. An Execute SQL task that runs first, creating a new record in an audit table in a SQL Server database, and returning the identity value for the new record, which is then used later on in the package execution. This audit table contains a "StartTime" column which tracks when the package execution started.
2. Do what ever "real work" the package does with whatever tasks are needed.
3. An Execute SQL task that runs last, and which updates the record in the audit table that was created earlier, including setting a value for the "EndTime" column in the audit table.

So do you have something like this in place already? If so, read on! If not, you may as well stop reading now, and start thinking about implementing something like this in your packages.

Because once you have this in place, all you need to do is run a simple SELECT statement like this:

SELECT *
  FROM [AuditTable]
WHERE [StartTime] < '2008-03-03 19:44:42.21'
ORDER BY [StartTime] DESC

This will give you a list of packages that have executed up to and including the package that logged the error, with the offending package at the top. You could also add a TOP 1 to your SELECT statement if you only wanted to see the one package that logged the error, but I have often found it useful to see what packages have run before the package with the problem, especially when there is logic in the master package that would dynamically run (or not run) the worker packages.

This is obviously not really high tech, but it's a useful little trick that will hopefully save you some time. Enjoy!

 

[1] Or any other error message - this is just the one that was leaping out at me today. I've been meaning to post on this little tip for months, and this just happened to be the error message that broke the blogger's back, as it were...