Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Capturing SQL Server Integration Services Package Errors Using OnError Event Handlers


By:   |   Last Updated: 2018-09-13   |   Comments   |   Related Tips: 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


next webcast button


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.



    



Learn more about SQL Server tools