locked
SSIS onerror event handler not being called RRS feed

  • Question

  • SSIS 2016, I added an event handler at the package level scope and added a sql task to write some system variables to a table.  Then I set the "ForceExecutionResult" to "Failure" on a task in the main control flow, so as to test the error handler.  For some reason, the event handler is not being called.  What should I check?

    Oh, and the package setting "DisableEventHandlers" is set to false.
    • Edited by shiftbit Friday, June 22, 2018 2:52 PM dfg
    Friday, June 22, 2018 2:49 PM

Answers

  • When you say "enable the OnError event handler for that", I assume you mean the extra task.  In other words, add another sql task with a bogus statement, and create an event handler for it, but dont add any tasks for that handler, so that it will bubble up to the package event handler?

    Id like to have one error handler to capture any/all errors for any task that causes problems during execution.

    yes

    If you want to capture it at parent level then no need to add error handler at task level

    Just add a task with invalid sql statement. That should be it

    By default errors will get auto bubbled to the package level and cause it to fail and fire its onError event


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by shiftbit Friday, June 22, 2018 4:02 PM
    Friday, June 22, 2018 3:11 PM

All replies

  • As far as I know ForceExecutionResult will not cause OnError event to fire. To test error, best option would be to add a test task with wrong sql statement and enable OnError event handler for that

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 22, 2018 2:59 PM
  • When you say "enable the OnError event handler for that", I assume you mean the extra task.  In other words, add another sql task with a bogus statement, and create an event handler for it, but dont add any tasks for that handler, so that it will bubble up to the package event handler?

    Id like to have one error handler to capture any/all errors for any task that causes problems during execution.

    • Edited by shiftbit Friday, June 22, 2018 3:06 PM dfgsdfg
    Friday, June 22, 2018 3:04 PM
  • When you say "enable the OnError event handler for that", I assume you mean the extra task.  In other words, add another sql task with a bogus statement, and create an event handler for it, but dont add any tasks for that handler, so that it will bubble up to the package event handler?

    Id like to have one error handler to capture any/all errors for any task that causes problems during execution.

    yes

    If you want to capture it at parent level then no need to add error handler at task level

    Just add a task with invalid sql statement. That should be it

    By default errors will get auto bubbled to the package level and cause it to fail and fire its onError event


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by shiftbit Friday, June 22, 2018 4:02 PM
    Friday, June 22, 2018 3:11 PM
  • Ok, ive done that, and no event handler except at the package level.  Still, the handler isnt called.  I know this because (1) no record inserted into my log table and (2) after right-click on the "bogus" task to execute it and getting a failure, I switch the event handler tab and see that the task within has NOT been run.

    Friday, June 22, 2018 3:20 PM
  • When you say "enable the OnError event handler for that", I assume you mean the extra task.  In other words, add another sql task with a bogus statement, and create an event handler for it, but dont add any tasks for that handler, so that it will bubble up to the package event handler?

    Id like to have one error handler to capture any/all errors for any task that causes problems during execution.

    yes

    If you want to capture it at parent level then no need to add error handler at task level

    Just add a task with invalid sql statement. That should be it

    By default errors will get auto bubbled to the package level and cause it to fail and fire its onError event


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Ok, I figured out why my handler want being hit.  I wasn't running the package, I was simply right clicking on the test task and executing that.  Once I ran the whole package (after disabling everything except the test task), I got my event handler to execute. 


    • Edited by shiftbit Friday, June 22, 2018 4:03 PM wer
    Friday, June 22, 2018 4:03 PM
  • When you say "enable the OnError event handler for that", I assume you mean the extra task.  In other words, add another sql task with a bogus statement, and create an event handler for it, but dont add any tasks for that handler, so that it will bubble up to the package event handler?

    Id like to have one error handler to capture any/all errors for any task that causes problems during execution.

    yes

    If you want to capture it at parent level then no need to add error handler at task level

    Just add a task with invalid sql statement. That should be it

    By default errors will get auto bubbled to the package level and cause it to fail and fire its onError event


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Ok, I figured out why my handler want being hit.  I wasn't running the package, I was simply right clicking on the test task and executing that.  Once I ran the whole package (after disabling everything except the test task), I got my event handler to execute. 


    Great

    Glad that you got it sorted!


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 22, 2018 5:20 PM