Problem
SQL Server Integration Services (SSIS) packages run when invoked by users or based on a configured schedule. Generally, packages are run in the background and it is hard to debug executable packages if you have not configured additional properties for debugging purposes. We should keep two things in mind when developing packages (1) Logging and (2) Exception Handling. As part of exception handling, there are several action points; at the container or task level, error output on source level, use of event handlers, etc. In this tip I will demonstrate how to capture errors to an Event Handlers table for review if a problem does occur.
Solution
There are many reasons errors get generated for a SSIS package:
- Auto failed task.
- Failed task due to the failure of ancestors control.
- Truncation happens or source or destination connection issue.
- Conversion failure happens.
- Issues from migrating files and files data.
- Package has an error due to privileges of OS controls.
- Other failure reasons.
When executing the package, errors could be generated anywhere inside the package. So, how do we get those errors from different action points from within the SSIS package?
In this tip I will show how to capture the errors to a table using Event Handlers.
Event Handlers in SQL Server Integration Services
SSIS has built-in event handlers that can be used to capture certain events when they occur during package execution. At run time, executable containers or tasks raise events. For example, an On Error event is raised when an error occurs. You can build custom event handlers for these events to extend the package functionality and we can specify the transaction and logging mode for the event handlers.
Event Handlers can perform multiple tasks like:
- Cleaning up temp data storage or managing auditing history based on the package running or finishing an action.
- Retrieving and capturing system information before a package executes.
- Setting an email alert when a warning occurs or when a task fails.
There are many run time events that we can use during the execution of a SQL Server Integration Services package. Here is additional information about the Event Handlers.
Below you can see the list of Event Handlers available in an SSIS package. We are going to focus on the OnError event handler, which is raised by an executable when an error occurs.
SQL Server Integration Service Package OnError Event Handler
Setup SQL Server Test Environment
First, we setup a SQL Server database table in order to capture the events.
IF NOT EXISTS( SELECT 1 FROM sys.DATABASES where NAME = 'ErrorHandling')
CREATE DATABASE ErrorHandling
GO
USE ErrorHandling
GO
IF EXISTS( SELECT 1 FROM sys.objects where object_id = object_id('PackageError') and type = N'U')
DROP TABLE PackageError
GO
CREATE TABLE PackageError
(
Pk_ID NVARCHAR(100),
Machine_Name Nvarchar(200),
Package_Name Nvarchar(200),
Task_Name Nvarchar(200),
Error_Code INT,
Error_Description Nvarchar(Max),
Dated Datetime
)
Add a Flat File to Data Flow Task
I am adding a Data Flow Task in the Control Flow.
Now, I am going to add Flat File source in Data Flow control and configure the file “ErrorHandling.txt” for testing purposes.
Now, my package is ready for execution, I will execute it.
My Package executes successfully. Now I am going to configure Event Handlers for capturing data to the error log.
Adding OnError Event Handler to SSIS Package
If you click on the Event Handlers tab in the package, you will see a drop down for the different Event Handlers as shown below. I am going to select OnError for the Package and add an Execute SQL Task to the Event Handlers section as shown below.
In the Execute SQL Task, I first configured the database connection to the ErrorHandling database. I setup the Expression for the sqlStatementSource as shown below where I am inserting data into the PackageError table.
Test Error Handling
To test this, I am going to change the flat file extension from “CSV” to “AVI” configured in the FlatFileSource. This should cause the package to create an error.
We can see the Flat File Source task failed, but the Event Handler task Execute SQL Task wrote data to the table. If we query PackageError we can see the errors that were generated.
Summary
Follow these simple steps to add logging for your SSIS packages so you can troubleshoot SSIS package execution errors.
Next Steps
- Check out the SQL Server Integration Services tutorial.
- Read more about SQL Server Integration Services Error Handling Tips.
- Read more about Debugging and Error Handling with SSIS Packages.
