Capturing SQL Server Integration Services Package Errors Using OnError Event Handlers


By:   |   Updated: 2018-09-13   |   Comments (4)   |   Related: More > Integration Services Error Handling

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.

Event Handlers

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.

Taking a Data Flow Task

Now, I am going to add Flat File source in Data Flow control and configure the file “ErrorHandling.txt” for testing purposes.

Flat file connection setup

Now, my package is ready for execution, I will execute it.

Taking a Flat File Source and setup file

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.

ssis package event handlers

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.

setting a expression builder

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.

Final Package result

Summary

Follow these simple steps to add logging for your SSIS packages so you can troubleshoot SSIS package execution errors.

Next Steps


Last Updated: 2018-09-13


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, January 10, 2020 - 1:48:40 PM - Jeannine Back To Top

Bhavesh,

Thank you for your article.  I seem to be having a small issue.  I have followed what you have in the article and if I execute the task directly from within the OnError Eventhandler the task runs fine and inserts to the table.  However if I force an error to occur in the package the task fails with the following error:

[Execute SQL Task] Error: Executing the query "INSERT INTO [PackageError] values('{95C8D381-0152-..." failed with the following error: "Incorrect syntax near 'Error'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I also can take the evaluated expression and run it in a SQL query window with no problem.  Can you possible help me with this issue?

Thank you,

Jeannine


Friday, January 10, 2020 - 11:10:26 AM - Andrew Oliner Back To Top

Thank you for what seems like a very useful article. One question/criticism. I am trying to copy the Insert command in sqlStatementSource. Since it's in an image, I can't copy and paste it, I have to retype it from scratch. I can't tell what you are doing with the quotation marks. It looks like there are often three ''' surrounding a comma. Is that " ' , ' " or ' " , " '?


Tuesday, May 28, 2019 - 12:05:44 PM - Peter Wietlicki Back To Top

Bhavesh,

Very useful artical, but there is one critical flaw in the proposed solution.

If you generate the sqlStatementSource string in the expression like you do then the sql query can fail depending on the contents of the System::ErrorDescription variable. For example if the error is related to a bad folder path the variable will contain an inverted comma and your method will fail.

A more robust solution would be to use parameters in the Execute SQL Task.

Thanks

Pete


Tuesday, January 08, 2019 - 5:08:17 AM - Chris Jenkins Back To Top

Hi Bhavesh,

I found this article really useful.  Thank you.

One thing I'd like to add is if you use [System::SourceName] rather than [System::TaskName] you'll get the name of the failing task rather than the name of the capturing task in the error handler.  You could also use a stored procedure to write to the DB rather than a direct insert.

Thanks,

Chris



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools