DBA Repository – Logging Errors in the SSIS Packages, Part 1

The DBA Repository I’m creating is going to aid me dramatically in proactively monitoring database servers throughout my organization but only if I can be certain the data in the repository is accurate.  In order to rely on my data I need to know when the data collection is experiencing errors.  Since I am using SSIS to gather collect all the data for the repository I’ll use it’s OnError event handling to record problems for all SSIS packages the repository requires.

Create a Log Table

Here’s the table I’ll use to hold SSIS errors:

CREATE TABLE [dbo].[Log] (
      [LogId] [bigint] IDENTITY(1, 1) NOT NULL
    , [Recorded] [datetime] NOT NULL CONSTRAINT [DF_Log_Recorded] DEFAULT (GETDATE())
    , [PackageName] [varchar](256) NULL
    , [TaskName] [varchar](256) NULL
    , [SourceName] [varchar](256) NULL
    , [ServerName] [nvarchar](128) NULL
    , [DatabaseName] [nvarchar](128) NULL
    , [ObjectName] [nvarchar](256) NULL
    , [Message] [nvarchar](max) NOT NULL
    , CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED ([LogId] DESC)
    )

Create the Default Error Handler

Tasks in SSIS are organized in a hierarchy of containers, the root container is the package itself and it may have ForEach Loop containers, Sequence Containers, etc that may hold other tasks.  When an OnError event is raised SSIS looks for the first available event handler configured beginning at the triggering task.  If SSIS does not find an appropriate event handler it bubbles up the container hierarchy until a handler is found or it reaches the package container and fails the package.

Enable the OnError Handler

Since my goal is to catch all errors and I really don’t want to create an event handler on every task in every package I can create a default OnError event handler at the package level and know that any error thrown will be caught and logged at the package level.

You can do this by clicking the Event Handlers tab then selecting the package name in the Executable drop down.  Select the OnError event in the Event Handler drop down are were ready to log errors:

Configure the Logging Task

In the event handler I dropped an Execute SQL Task to perform the insert to the dbo.Log table created earlier.  The trick for this task is to make the SQL Statement dynamic so it captures the data required to troubleshoot the issue even when the error bubbled up from lower in the container hierarchy.  To do this, configure the SqlStatementSource property of the Execute SQL Task with the following expression:

“INSERT  INTO dbo.[Log] (
ServerName
, SourceName
, PackageName
, TaskName
, [Message]
)
VALUES  (‘” + @[User::SQLServer] + “‘
, ‘” + @[System::SourceName] + “‘
, ‘” + @[System::PackageName] + “‘
, ‘” + @[System::TaskName] + “‘
, ‘” +    REPLACE( @[System::ErrorDescription], “‘”, “” )  + “‘)”

This expression will insert the SQL Server the package was collecting from (User::SQLServer), the task the error originated from (System::SourceName), the package name (System::PackageName), the task that is handling the error (System::TaskName), and the error message (System::ErrorDescription).

Those data will typically get me enough information to begin troubleshooting what happened so I can correct the issue and ensure accurate Repository data.

The Next Step

In the next entry I plan to describe a similar method for handling errors lower in the container hierarchy and preventing the error from bubbling up allowing the package to proceed in the event of an error.

4 Responses to DBA Repository – Logging Errors in the SSIS Packages, Part 1

  1. Pingback: SQL-ution » DBA Repository – Gather Database Connection Information

  2. Xxl says:

    Where do you assign a name of the sql server to a user variable (User::SQLServer)?

    Thanks!

  3. DBA Dave says:

    That’s a variable I created scoped on the to the package container (making it global to the package). That variable can either be set through a configuration file, manually defined by just inputting a string in the variables window, or programatically as the package is running.

    In the DBA Repository project, most of my packages are looping through all the server instances on the network to perform work on each instance. Nearly all of the tasks in the DBA Repository project’s are in a loop container. What I typically do is set the User::SQLServer to the value of the currently connected server each iteration of the loop so I can log where the repository was working when it encountered an error.

    Check out this article, it describes how I perform the assignment of User::SQLServer.

  4. Sanjeev Damle says:

    This worked great… I was struggling with the double quotes in the ErrorDescription.

    Thank you very much

Leave a Reply

Your email address will not be published. Required fields are marked *