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