SQL Server Integration Services (SSIS) packages are often run in the background, making it difficult to debug them when errors occur. To effectively troubleshoot and resolve issues, it is important to configure additional properties for debugging purposes. Two key aspects to consider when developing SSIS packages are logging and exception handling.

Exception handling involves identifying potential error points at the container or task level, error output on the source level, and the use of event handlers. In this article, we will focus on capturing errors to an Event Handlers table for review when problems occur.

Solution

There are various reasons why errors can occur in SSIS packages, such as failed tasks, truncation issues, conversion failures, and more. To capture these errors, we can utilize Event Handlers in SQL Server Integration Services.

Event Handlers are built-in features in SSIS that allow us to capture specific events during package execution. These events can be raised by executable containers or tasks, such as the OnError event that is triggered when an error occurs. By creating custom event handlers, we can extend the functionality of our packages and specify transaction and logging modes.

Event Handlers can perform multiple tasks, including cleaning up temporary data storage, managing auditing history, retrieving system information, and setting email alerts for warnings or task failures. There are several runtime events available for use in SSIS packages.

Let’s focus on the OnError event handler, which is raised when an error occurs. To set up this event handler, we need to follow these steps:

1. Create a SQL Server database table to capture the events.
2. Add a Data Flow Task in the Control Flow.
3. Add a Flat File source in the Data Flow control and configure the file for testing purposes.
4. Configure the Event Handlers tab in the package and select OnError.
5. Add an Execute SQL Task to the Event Handlers section.
6. Configure the database connection and the SQL statement to insert data into the error log table.

Once the event handler is set up, we can test it by intentionally causing an error. For example, changing the file extension in the Flat File source can trigger an error. The error will be captured in the Event Handlers table, allowing us to review and troubleshoot the issue.

Summary

By following these steps, you can add logging to your SSIS packages and effectively troubleshoot execution errors. Event Handlers provide a powerful tool for capturing and managing errors, allowing for efficient debugging and problem resolution.