Capturing SQL Server Integration Services Package Errors Using OnError Event Handlers

By:   |   Comments (7)   |   Related: More > Integration Services Error Handling


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.


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')

USE ErrorHandling

IF EXISTS( SELECT 1 FROM sys.objects where object_id = object_id('PackageError') and type = N'U')
DROP TABLE PackageError

 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


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

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, July 5, 2021 - 12:56:13 AM - Sundar Anupindi Back To Top (88945)
I am also having the same issue. When executed from Error Handler it works.
But when the Package fails, it says that there is error in your syntax.
What is the exact syntax to be used it the Execute SQL Task?
Did it worked for any one?

Monday, July 27, 2020 - 5:20:30 AM - Mangesh Back To Top (86196)


I'm also facing the same error like Jeannine. I also have some doubts. In this case package will fail or success? What should be the Error Output property for the destination.

Do we need to set any results property or user parameter for this?



Wednesday, May 27, 2020 - 3:49:32 PM - Jeison Back To Top (85785)

Here is the correct script

"INSERT INTO [PackageError]





   ,"+(DT_WSTR, 50)@[System::ErrorCode]+"



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


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,


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

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


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.



Tuesday, January 8, 2019 - 5:08:17 AM - Chris Jenkins Back To Top (78660)

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.



get free sql tips
agree to terms