Exclusives

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:

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...

Read more...