When working with SQL Server, it is important to have a mechanism in place to log errors for debugging purposes. Logging information such as when data is extracted, loaded, the source, the destination, and other vital details can be extremely helpful in troubleshooting issues. In this article, we will explore a solution for logging error events in SQL Server Integration Services (SSIS) packages.

Step 1 – Create the Errorlog table

The first step is to create a table that will serve as the repository for storing the logging data. This table can be created in the stage database. Let’s assume our stage database is called “TesTDB” and the Error log table is named “[dbo].[ErrorLog]”. The following script will create the table:

USE [TesTDB]
GO

CREATE TABLE [dbo].[ErrorLog](
    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    [PackageLogID] [int] NULL,
    [PackageName] [varchar](100) NULL,
    [TaskName] [varchar](100) NULL,
    [ProcedureName] [varchar](100) NULL,
    [ErrorCode] [bigint] NULL,
    [ErrorMsg] [varchar](5000) 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]

The ErrorLog table has several columns that capture important information about the error event. These include:

• ErrorLogID: Identity column for unique identification
• PackageLogID: Recent ID of the package
• PackageName: Name of the package
• TaskName: Name of the task that raised the event
• ProcedureName: Name of the procedure
• ErrorCode: Event code generated when the OnError() event triggers
• ErrorMsg: Event description for the error
• PackageDuration: Duration of the package execution in seconds
• ContainerDuration: Duration of the task that raised the event
• ErrorDate: Date and time of the error occurrence

Step 2 – Build the logging functionality

Next, we will use event handlers in SSIS to demonstrate the custom logging ability. We will focus on the OnError() event handler, which captures error-related information. To create the event handler, follow these steps:

1. Select the task or container on which you want to create the OnError() event handler.
2. Click on the Event Handlers tab.
3. Select the OnError event handler from the drop-down menu.
4. Create an “Execute SQL Task” in the working area and rename it to “Error Log Task”.
5. Change the connection manager to the stage database.
6. Use expressions in the Execute SQL Task Editor to dynamically change the SQL query for the Error Log.
7. Set the SqlStatementSource property of the Execute SQL Task to the following query:

"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())"

Make sure to typecast all numeric fields to strings in the query to avoid any exceptions. The SqlStatementSource property concatenates various parts of the dynamic SQL statement to insert the error information into the ErrorLog table.

Step 3 – Test the logging functionality

To test the logging functionality, you can create a data flow task that populates employee information in a destination table. Implement the logging mechanism on the “Data Flow Task” task. For example, if you specify an incorrect server address in the connection manager, the task will fail and trigger the OnError() event. As a result, the ErrorLog table will be populated with the error information.

After running the package, go to the Event Handlers tab and select the Data Flow Task from the Executable drop-down menu. If the Error Log Task runs successfully, check the Error Log table to verify that the error message has been logged.

Conclusion

In this article, we have explored how to easily track and store errors related to SQL Server Integration Services packages. By implementing custom logging using event handlers and a dedicated ErrorLog table, we can effectively debug and analyze errors that occur during package execution. This logging mechanism is particularly useful when running packages as scheduled jobs without a GUI interface for real-time analysis.