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.
