“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”.
This entry was posted in SQL Server, SSIS. Bookmark the permalink.

18 Responses to “OnError” versus “OnTaskFailed” Event Handler in SSIS

  1. shahriar says:

    Hi
    In the …………
    EventHandels -> Executable = The main root (package) -> EventHandler (Onerror)
    ……… i have a DFT that is making the Error text file and in the ……………..
    EventHandels -> Executable = The main root (package) -> EventHandler (OnTaskFailed)
    …….. i have the SMT object that sends an email with the attached Error text file

    when i run the package the EventHandler (OnTaskFailed) won’t even run it remains while , not green and not red, it’s like it dosen’t get to this event handler.

    to by pass this i put the Email SMT object into EventHandler (Onerror) that is why i get 3 emails, i cant get it to send 1 email

    story short i want to send one email when the package failes.

    sincerely
    SH

  2. Hi Shahriar,

    As in when your DFT fails, your package will also fail & then you need to send an email out. So for this, you can use ‘OnTaskFailed’ event handler of DFT and put a Send Email task there which is like sending email if your package fails.

    Thanks,
    Munish Bansal

  3. Jerry says:

    Hi Munish,

    Im ok with logging of error in SQL table, but i noticed that the error are logged 3 times and i would like to know if there is a way to choose which of those 3 is i want to log.
    From the error description below, i just want to log the error description #1 which i think is enough to get the details that cause to raise an error.

    1. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “The statement has been terminated.”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK_EmployeeTransactionHistory’. Cannot insert duplicate key in object ‘esa.EmployeeTransactionHistory’.”.

    2. The ProcessInput method on component “Copy to EmployeeTransactionHistory table” (1369) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

    3. Thread “WorkThread0″ has exited with error code 0xC0202009.

    Currently, my implementation is to used a Execute SQL Task which i stated the INSERT Statement and use ? as parameter then i add new item in Parameter mapping (see the details below)

    Variable Name : System::ErrorDescription
    Direction : Input
    Data Type : VARCHAR
    Parameter Name : 0

    and this variable will be assigned to the ? parameter of INSERT Statement.

    Thanks,

  4. Hi Jerry,
    You might be using OnError event to log errors in SQL Server table. As I stated this gets fired multiple times. So you can choose other events instead of OnError like OnTaskFailed etc from the list of available events in the Details tab of logging.

    Thanks,
    Munish Bansal

  5. Jerry says:

    Hi Munish,

    See my component structure

    – Sequence Container
    > Data Flow Task
    > Script Task

    The logging of error details in SQL database is handled by the Sequence Container event handler (OnError) which will triggered when an error occured in any of the components inside the container (Data Flow Task or Script Task).

    If i use the OnTaskFailed of Sequence the ErrorDescription variable is not available, or else i’ll use the script task to manipulate the error and get the first one.

  6. Hey Jerry,
    ErrorDescripton variable is there for all these events.
    And also you may handle the OnTaskFailed event of the DFT itself, not at the container level.

  7. Divya says:

    Hi,

    I want the error description on error in package which i want to log it into sp. Could you help with this!!

  8. Divya,

    You can get it from the ErrorDescription system variable in the OnError Event handler.
    But if you want to get it in the data flow task, you can use ErrorID column which comes from the error output of a transformation/OLE DB Command etc.
    You can use a Script Component to convert the ErrorID to actual error description using the below code:

    Row.ErrorDescription = Row.ErrorCode.ToString + “: ” + ComponentMetaData.GetErrorDescription(CInt(Row.ErrorCode))

    ‘ErrorDescription’ is the column added to the output of the script component.

  9. regcure says:

    wow, awesome post, I was searching what are the best registry cleaner and ready to pay to solve the problem. luckily i found your site by yahoo, many userful stuff here, now i’m a bit clear on what should i do. I’ve bookmark your site and also add rss. keep us updated.

  10. Thanks for your comments, regcure !

  11. Zahra says:

    Dear Munish I would be grateful if you could help me resolving my issue with SSIS Event Handler.

    Brief Scenario:

    >Parent Package contains a Foreach loop that calls a Child Package in every cycle of its loop

    >Child Package contains a DFT for transforming data btw two DBs

    > An OnError Event Handler defined to handle errors when an error occurs while data transformation (for example: Connection cut)

    > When Error arises Error handler works fine. But after completion it doesn’t return to the Parent package, in order to continue the foreach loop iterations from where it was coming out!

    Thanks,

    -Zahra

  12. Hi Zahra,

    This happens due to event bubbling, since by default MaximumErrorCount allowed on a task is 1, so by the time you get an error on the ExecutePackage task which is caused due to the error in child package, it propagtes this error to parent and that makes it to fail.
    So either increase the value for MaximumErrorCount to some value or set the Propagate property on the child package to false (which is true by default).
    This Propagate setting you will find under system variables.

    Thanks,
    Munish Bansal

  13. aray says:

    Hello Munish

    I have execute sql task which executes a store procedure and raises the error if the record already exists and sends an email using the send mail task,otherwise it will take the parameter from the stored procedures and continues loading the fact table.But my task is getting failed on raising the error in the stored procedure but it sends an email saying records already exists.

    I dont want my task to be failed but it should come out in a peaceful manner if it finds the record and raise error.

    Please i would really appreciate your reply.

    Thanks
    Aray

  14. i really find registry cleaners very necessary to improve the system performance of a desktop PC :”.

  15. Jyoti says:

    Hi Munish,
    I have created an SSIS package programmatically in C#. It works fine. However, I now want to add an event handler for the package itself. I want to associate an “Execute Process Task” with this handler, so if the package encounters any issues, the event handler should get fired and that should inturn run the script in the “Execute Process” task. This script is suppose to send me an email saying that the package failed.
    private static void CreateEventHandlers(EBIProduct product, Package package)
    {
    DtsEventHandler ehOnError = (DtsEventHandler)package.EventHandlers.Add(“OnError”);
    Executable executable = TaskHostHelper.AddExecProcessTaskToEventHandler(ehOnError, “Send Failure Email”, CIProject.ProcessingProduct.Project.EBIMethodConsoleExePath, “@[User::” + PackageVariables.SendFailureEmail + “]”);
    }
    I call this method in the main method where i create the package. However when i build it, it does not create any event handler. :(
    Can you suggest something??
    tks!!
    Jyoti

  16. Veena says:

    Hi,

    I have a package which calls Package1 and Package2. I have implemented an event handler which fires OnError,
    I have a ExecuteSQL task that logs the Error into a table.

    There is an error in Package1. But in the table I could see that there are two entries.

    When I used breakpoints to debug I could see that the eventhandler is being called twice.

    It shows me the same behavior for both OnError and OnTaskFailed events.

    Please advice..

    Thanks and Regards,
    Praveena.

  17. Neeraj Matta says:

    Very well explained. Better than MSDN!!!

    Cheers,
    Neeraj

  18. Fahad Mirza says:

    Hi,
    “Hey Jerry,
    ErrorDescripton variable is there for all these events.
    And also you may handle the OnTaskFailed event of the DFT itself, not at the container level.”

    “ErrorDescripton variable is there for all these events.”-
    I am not able find the ErrorCode and ErrorDescripton variable on any “OnTaskFailed” event.Using BIDS(Visual Studio) 2008.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s