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…

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in BI, SQL Server, SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s