SSIS - Event Handling with "OnError" or "OnTaskFailed"

  • Article

Introduction

There are many "events" that we can use during the execution of a SQL Server Integration Servives (SSIS) Package. In all available events, this article is going to highlight two events that are very important to display custom errors message. In this way you can make accurate statements about the failures or errors occurrences in one or more Tasks in the Package.

We may require validation for a full treatment of each error, because the failure package (in part or full) would cause data loss or failure during an important Task processing of data integration on our(s) database(s).

Depending on the criticality of the SSIS Package processing, we needed to enable an event handler for a Task or for all Package, to get details about what happened and preferably identifying the Task and the values ​​of some variables that can help the developer to reproduce the problem to find quickly the appropriate solution. In a few cases, such as file processing (import or export data) can also be necessary to implementation of one or more Tasks within an event to ensure that other files will be processed.

Difference between these Events

Apparently a Failure event maybe similar to an Error event, but there is an important difference between the two events that is the context where each one of these events is triggered.

We will have the option to enable an event for create a treatment problems in each of the failed Tasks or else common treatment problems on entire Package. Then you can choose to use one of these events based on your requirements or by need to get information about System Variables or Packages Variables (user-defined variable). In these situations we can choose the OnTaskFailed or OnError event.

So you want to run one of these events or both without interrupting the Package processing for failures or errors were found during the validation, in other words before you can run the Package, then you must modify the "DelayValidation" property on Package or Tasks that need to be executed with the value equal to "TRUE".

See this Properties window in the image below

Warning
You can do the Package validation or in your Tasks before running. It's a good way to find errors, but it significantly increases Package cost and time processing. It's recommended that DelayValidation property be changed only when necessary and only on Tasks specific.

Let's see now these two Event Handlers in SSIS.

OnError

This event is generated when one or more errors occurred due to a problem within the Package for any reason within the related Data Processing objects.

The scope of this event is large, where the developer may add a common treatment errors on entire Package, the System Variables can display general data, does not allowing the accuracy of cause of the problem.

We can say that potential problems that are not expected to error handling may require a greater setup burden on the developer to identify the error cause and location to implement proper error handling for future uses.

This event handler can be enabled selecting any of the Tasks on the Package and clicking on the "Event Handler" tab in your SSIS Package.

See this Event Handler in the image below

The errors presented in this event may also be related to more than one Package or component used, so the "OnError" event can be run several times, one for each error occurred inside the Package. Then, the developer must be aware that handler is being added to this event must be well defined to prevent the Package has a new error, interrupting the execution of a or more Tasks.

OnTaskFailed

This event is generated only once when a Task "failure", independently of failure occurrence be in the Package context or just in a Task specific.

See this Event Handler in the image below

This event will run for each Task involved in this Package. We can verify this by enabling a Task on "OnTaskFailed" event handler.

After enabling event, we can return messages to a Data Administrator or also perform other tasks as data processing and log storage of your Package, for example, the failure origin using the "SourceName" system variable.

So we need access to the Task properties and select the "Parameter Mapping" tab.

After accessing this guide, then It's possible to add a global variable (available to all Package) how "Parameter" passing by-value and by-reference are accomplished in memory and thereby to receive the Task name with the problem.

There are several other System Variables available for data important from your environment and also settings related to SSIS Package. check the information necessary to make the conditions for implementation of their Package at the time of failure to be able to exactly reproduce the problem for your action and subsequent correction.

You can perform this action by sending an e-mail or add data in a Log table, so analyze efficiently the problem and implement the appropriate data treatment (for example: converting data type or when we use data sources to move a problem file to another folder).

See this Task window in the image below (click to enlarge)

Conclusion

It's important to know the differences in behavior between Error and Failed events assuming that the developer can make the most of the resources available in your Package,be it a SQL Server database or an external data source, as an Excel file(.xls ou .xlsx), "tabbed text" or "comma separated" formats (.txt ou .csv) or even on other Relational Database Management System (RDBMS) database.

Define the possible failures or errors that can occur in an SSIS Package helps make the whole process with efficient data integration, that are faster and self-sufficient.

Most issues can be resolved with their involvement, if you understand the concepts of each of these events and apply the treatment integrity data for each situation identified in your environment against the requirements defined in your project.

References

See Also

Other Languages

This article was awarded the gold medal in the TechNet Guru of May 2014