There was an error in this gadget

Tuesday, 10 May 2011

“OnError” versus “OnTaskFailed” Event Handler in SSIS

There are lots of event/event handlers which we can make use of during the package execution. Among all those events/event handlers, two of them are very important for logging the custom errors; which may occur due to failure of some of the tasks of a package.

We may be required to log such errors once upon the failure of the complete package or we may want it for each & every task failed during the package execution.
So depending upon these requirements we have two types of event handlers in SSIS:
1.      OnError
2.      OnTaskFailed

OnError: This event is raised when an executable gets some errors due to any reason. This may be due to failure of any of the tasks included in the package.

OnTaskFailed: This event is raised when a task is failed irrespective of the complete package failure.

The important difference between these two is explained below:

Let’s suppose we have a package (Package 1) which has many tasks; one of them is ExecutePackage task. Now this ExecutePackage task executes another package (Package 2) which in turn contains some tasks.

Pacakge 1:  Data Flow Task -> Script Task -> ExecutePacakge Task (Package 2)
Package 2:   Script Task

Now suppose Script task of the package 2 gets failed due to some errors (like with some wrong code inside). Then OnError Event of the package 1 will be raised only once.
But OnTaskFailed event of the package 1 will be raised twice; once due to the failure of Script Task of the package 2 and second time due to the failure of the ExecutePackage task of the package 1 (which gets failed due to failure of package 2).

Observations:
  1. OnError event is raised when ever there are some errors occurring in any task of the package. These errors may be more than one for a single executable/component like in case of DFT components. And thus OnError event will be raised multiple times, but as explained in the example we took Script Task which is raising only one error.
  2. OnTaskFailed event is raised when a task is completely failed. And this will be raised for each & every task involved in that package/parent task.This can be checked by putting a Script Task in the OnTaskFailed event handler of “package 2” with displaying the Source of the failure by using a System variable “SourceName”.

No comments:

Post a Comment