Creating a Solid SSIS ETL Solution: Part 1 – Package and Error Logging (cont.)

Creating a Solid SSIS ETL Solution: Navigation
Part 1. – Package and Error Logging
Part 1. – Package and Error Logging (cont.)
Part 2. – Package Configurations
Part 3. – Package Template Overview
Part 4. – Email Task and Package Execution Report

In my last post, Creating a Solid SSIS ETL Solution: Part 1 – Package and Error Logging, I forgot to show how errors are logged when a fatal error occurs during package processing. Fatal error capture is essential in troubleshooting a package failure. No logging solution is complete without it.

Keep ‘Em Separated

When I started creating my package template I had originally tried to put all logging and error capture processes in the event handler tab of the template package. I wanted to keep the meta, administrative actions separate from the control flow tab where the main processing of a package takes place. I tried to have the package and detail log start and end tasks fire via the OnPre/PostExecute and OnPre/PostValidate event handlers, but these two event handlers didn’t function as I expected. There were two handlers which did function quite well for my needs: the OnTaskFailed and OnError handlers.

Whelps Left Side! Handle it!

One thing SSIS does a good job of is error messaging. The error messages that are returned when a package runs and fails are easy to read and very informative. When a package failure occurs I want to know four things:

1. Which task failed
2. What error code is associated with the failure
3. What error message is associated with the failure
4. If associated with data, which row of data failed (this is something the built in logging does not provide)

To collect which task failed, I set up a simple script task in the OnTaskFailed event at the parent package level. This script fires when an error occurs, and captures the name of the current task into a variable called v_SSISPackageLogTaskName.

OnError Handler: A Little Hyper, but Gets the Job Done

Now the OnError handler is a bit different. It fires once for the current task that failed, once more for each parent object, and finally also at the package level. If a task is nested in a ForEach loop container the OnError handler will fire once for the task failure, once for the ForEach failure when the task returns an error, and one last time for package failure. Usually only the first message gives enough information to be useful, but just to be sure I record them all. In my package template each time the OnError handler fires it does a few things sequentially:

1. First it changes the log result type to 3, which is for system failure
2. Next it saves the error message to the variable v_SSISPackageLogResultMessage
3. Then it appends the error code and message to the v_SSISPackageLogMessageForEmail variable, which will be sent out in an email when the package completes
4. Lastly it sends all this information including the SSISPackageLogDetailRowID, if it exists (if a detail row has not been created, …DetailRowID is -1), to the prSSISPackageLogDetailRecordError sproc

The prSSISPackageLogDetailRecordError sproc will then either update the open Detail record with the error, or will record a new detail row with the error information. Recording the error information in this way makes it very easy to track down an error, and figure out exactly what failed.

Next: Package Configurations

Next I’ll show how my template uses package configurations and a package configuration table in the AdminDB to store connection information. This makes it very easy to port your packages between your dev, test, and production environments without having to rebuild the package deployment. ==>

I’ve decided to change my posting frequency to every other week, so be sure to check back on or before Dec. 12th for the next installment of this series.

Example Files

Files and Test Data