Gracefully Handing Task Error in SSIS Package

RMehta — Thu, 24 Apr 2008 11:46:00 GMT

I am sure that there is already some blog or article about this. But, I encountered this today and decided to go ahead and blog anyway. I had a situation, where I expected at certain times (the first of every month), a task to fail, but regardless, I wanted the package to continue to run with success. One other issue, I had, was that I did not want to Error Handler of the package to kick off in case of this task failure. Also, since the task was in a Sequence Container, it was causing the Sequence Container execution results to be Failure thus invoking the "On Failure" workflow.

This is the default behavior where the task failure for the “Drop active jobs partition” SSAS DDL task invokes the error handler at the package level (which is where I have a global error handler) and invokes a failure of its parent container. Since I know that this task is bound to fail the 1^st of every month (In this scenario, I am dropping, creating and processing a monthly SSAS partition on a daily which won’t exist on the 1^st of a given month), I want to gracefully “ignore” this failure and not signify a failure or write error rows in my error log tables. So, the way to handle this scenario was..

1. Create an “On Error” Event Handler on the task “Drop active jobs partition” – You can leave the event handler blank with no tasks

2. To Prevent the error from going further up the chain, open up the System Variables from within the On Error error handler of the task and change the Propogate property’s value to False

This should provide the desired result where the error is contained within the task

Optionally, you can also choose to set the ForceExecutionResult property of this task to be Success so that it always signals a success – Even with this, you still need to follow the above steps to prevent errors from bubbling up in the package.

Rushabh Mehta : Event Handler

Gracefully Handing Task Error in SSIS Package