Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

On Error () Logging by Using Event Handlers

By Arun Mallick,

Sometimes we need to log a lot of things for debugging purpose, like when the data is extracted, when it is loaded, the source, the destination, and some other vital information. Here some is needed mechanism that will log the information regarding the error. Like, I want to log the Error event with necessary information that will help me debugging it.

Solution

We can use the event handlers provided in SSIS package to achieve our goal. But before that we must have some provisions for storing our logged data. For the same and to go ahead, we have to follow the below steps:

Step 1 - Create the Errorlog table
The Errorlog table will be our repository for storing the logging data. This we generally create in our stage database. Let the stage database be TesTDB and the Error log table is [dbo].[ErrorLog].

The script below will create a table into which we are going to log events from our SSIS packages.

USE [TesTDB]
GO
/****** Object: Table [dbo].[ErrorLog] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[PackageLogID] [int] NULL,
[PackageName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TaskName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrcocedureName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorCode] [bigint] NULL,
[ErrorMsg] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PackageDuration] [int] NULL,
[ContainerDuration] [int] NULL,
[ErrorDate] [datetime] NULL CONSTRAINT [DF_ErrorLog_ErrorDate] DEFAULT (getdate()),
CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] GO
SET ANSI_PADDING OFF

Below are the descriptions for each column in ErrorLog table.

Column_name Type Description
ErrorLogID int Identity Column
PackageLogID int Recent Id of the package (this will uniquely identify each run of the package)
PackageName varchar Name of the Package
TaskName varchar Task that raise the event
ErrorCode bigint Event code generated when OnError() event triggers
ErrorMsg varchar Event description for that event
PackageDuration int How long has the package been executing for in Sec.
ContainerDuration int How long has the task that raise the event been executing for.
ErrorDate datetime The date on which the error occur

All of the information that we are going to log will come straight out of SSIS. Most of the information is provided by system variables.

Step 2 - Build the logging functionality

We are going to use event handlers to demonstrate the custom logging ability of SSIS. Out of the many useful event handlers provided in SSIS, we will demonstrate OnError() event handler for capturing error related information

Select the task or container on which you want to create OnError() event handler.

Then Click on the Event Handlers tab.


Select OnError Event handler from the right drop box and click on the middle. Here we are going to create the OnError event for Data Flow Task.

Drag one "Execute SQL Task" to the working area. Error information will be populated to the Error Log Table by using this SQL Task.

Rename the task to Error Log Task and change the connection manager to Stage.

We mostly use system variables to get the information we are interested in. So we will use Expressions in Execute SQL Task Editor that will dynamically change the SQL Query for Error Log.

Select SqlStatementSource from Property Expressions Editor and click on the ellipse button.

On the Expressions Editor type the following Query in the Expression box.

"INSERT INTO [TesTDB].[dbo].[ErrorLog]
([PackageName]
,[PackageLogID]
,[TaskName]
,[ErrorCode]
,[ErrorMsg]
,[PackageDuration]
,[ContainerDuration]
,[ErrorDate])
VALUES
(
'" + @[System::PackageName] + "'
," + (DT_STR, 15, 1252) @[User::PackageLogID] + "
,'" + @[System::SourceName] + "'
," + (DT_STR, 15, 1252) @[System::ErrorCode] + "
,'" + @[System::ErrorDescription] + "'
," + (DT_STR,6, 1252) DATEDIFF("ss", @[System::StartTime] ,GETDATE()) + "
," + (DT_STR,6, 1252) DATEDIFF("ss", @[System::ContainerStartTime] ,GETDATE()) + "
, GETDATE()
)"


Click on the Evaluate Expression button for checking the Query.

Here we need to type cast all the numeric fields to string otherwise Expression evaluator will throw an exception. All we are building here is a value for the SQLStatementSource property of the Execute SQL Task. This property is of type string. We're concatenating various parts of our dynamic SQL statement and we want to concatenate strings, not numbers. Then Press OK.

Now the error log for that task is set.

Step 3 - Now test it !

For testing, here I have taken a dataflow task that will populate the employee information in our destination table. I implemented the logging mechanism on "Data Flow Task" task.

And here I didn't specify the correct server address in the connection manager. So this task will fail and the corresponding OnError() event will rise. As a result the ErrorLog table will be populated with the Error information.

Now go to Event Handlers Tab and select Data Flow Task from the Executable:The Error Log Task runs successfully. Now check the Error Log table. The error message from our script task has gotten logged successfully. Now we can keep track of all the errors occurring while our package will be scheduled for a job, as we don't have GUI interface there to analyzing things.

Conclusion

In this article, you have seen how we can easily track all the errors related to the package and can store them for debugging and analysis.

Total article views: 7793 | Views in the last 30 days: 11
 
Related Articles
FORUM

OnError Event Handler

OnError Event Handler

BLOG

SSIS - Event Handler Performance

Event handlers are a great tool for managing errors and other events that occur during the execution...

BLOG

SSIS - Event Handler Performance

Event handlers are a great tool for managing errors and other events that occur during the execution...

FORUM

Issue with SSIS OnError Event Handler

Issue with SSIS OnError Event Handler

FORUM

Event Handlers in SSIS(OnError & OnPostExecute)

Event Handlers in SSIS(OnError & OnPostExecute)

Tags
dts    
error handling    
error logging    
handle    
integration services (ssis)    
package    
sql server    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones