Skip to content

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

November 28, 2010

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

  1. Prashant permalink

    The article presented by you was very nice and discreptive.

    I had one query about the ssis logging;
    I am using onerror event handler at the package level in order to capture errors. In this onerror event i am logging the package execution result in database table and also to the text file on the local drive.
    When the package execution is success it logs one row of data in the database table and the file which is fine but when there are multiple errors generated in the package then its logs multiple rows in the database table and text file which was not i wanted.
    I only wanted to log the package execution result as one row in the database table and in the text file with the error details.
    Please let me know on this.


    • Jonathan Amen permalink

      You could instead have the onerror event fire a task to append the error to a string variable. Then during the last step of the package write it to the table and disk. Make sure that task is on failure for the global container, so it only fires if something fails.

  2. Prashant permalink

    Thanks Jonathan,
    I tested the solution provided by you and its working as required when the package fails.

    Have One query :
    My package design is similar to the example in your article with a SEQUENCE CONTAINER and a SCRIPT TASK(in your case its GLOBAL CONTAINER and EMAIL task).

    When the package fails, i would be logging the details in database and the disk on the event OnTaskFailed event of the SEQUENCE CONTAINER.

    But when the package executes successfully i would be logging the status using the SCRIPT TASK in control flow.

    Actually the SCRIPT TASK which i have added was to specifically log package success details in database and the disk.

    What do you say about my logging of FAILURE and SUCCESS details on two different tasks.

    Sorry, i am a beginner to SSIS and so want to clarify my doubts before actually implementing

  3. Raj permalink

    Thanks for the good article.I am trying to create the error handling system by firing a query in SYSSSIS log table

    Why does the SOURCE column of the SYSSIS log table shows the package name instead of the task name because i have got error in one of the tasks.The task is inside the sequence container of the package,so it has to show the task name rt?

    select source from msdb.dbo.sysssislog where (upper(event) = ‘ONERROR’) shows the name of package instead of the name of errored task

    Any sugestion welcome

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: