Using Event Handlers with Checkpoints (Part 3)

  • Aaron Akin

    Old Hand

    Points: 359

    Comments posted to this topic are about the item Using Event Handlers with Checkpoints (Part 3)

  • prasanna-750812

    SSC-Addicted

    Points: 483

    Hi Aaron Akin,

    I have read your article about "Using EventHandlers with Checkpoints". It is very usefull for me. But OnError eventhandler is not executing for the second time even though i did all the changes which you mentioned in the article. Is there any other way to handle this sutition.

    Please help me to handle this sutition.

  • Aaron Akin

    Old Hand

    Points: 359

    prasannabe06 (3/11/2009)


    Hi Aaron Akin,

    I have read your article about "Using EventHandlers with Checkpoints". It is very usefull for me. But OnError eventhandler is not executing for the second time even though i did all the changes which you mentioned in the article. Is there any other way to handle this sutition.

    Please help me to handle this sutition.

    Dumb question, but you do have checkpoints enabled properly, correct? Also, make sure the following are set in your package:

    1. Make sure the FailPackageOnFailure property is True on the task in the package control flow.

    2. In the OnError event handler for your task with the error, make sure you put all of the event handler tasks into a For Loop container that will iterate only once.

    3. Make sure the FailPackageOnFailure property is True on the For Loop container in the OnError event handler control flow.

    4. Make sure the FailParentOnFailure property is True on all tasks within the For Loop container in the onError event handler.

    5. Make sure the ForceExecutionResult property is True on the last task in the For Loop container in the OnError event handler.

    6. Make sure you delete the existing checkpoint file if it exists before executing the package.

    Let me know if this solves your problem.

  • prasanna-750812

    SSC-Addicted

    Points: 483

    Hi Akin,

    i have followed all the steps which u have explained, even then i am not able to get my result.

    Let me explain you my requriment. I have an Execute process task in Control flow. If i get any error in this task, i will read the error message and write it in to a variable from OnError EventHandler. From OnTaskFailed EventHandler i have one more Excute Process task which will send the mail with the error message.

    Steps which i followed are

    1.FailPackageOnFailure property is set as True on the Execute process task in the package control flow.

    2.I added one script task along with other tasks in the OnError EventHandler and i set ForceExecutionResult for this script task as Failure.

    3.I kept all the tasks in the OnErrorEventHandler in the ForLoop Container and i set FailPackageOnFailure property is True on the For Loop container.

    4.For all the tasks inside the ForLoop container i set FailParentOnFailure as true in the OnErrorEventHandler.

    5.I kept all the tasks in the OnTaskFailed EventHandler in the ForLoop Container and i set FailPackageOnFailure property is True on the For Loop container.

    6.For all the tasks inside the ForLoop container in OnTaskFailed EventHandler, i set FailParentOnFailure as true.

    when i run this for the first time ,i am getting an error in the last Script Task in OnErrorEventHandler and it is not moving to the onTaskFailedEventHandler. For the Second time if i run with out deleting the checkpoint i am getting the same result as before.

    What i actually required is, if i run the package both OnError Event Handler and ONTaskFailed EventHandler must be triggered with or without checkpoint.

  • Aaron Akin

    Old Hand

    Points: 359

    Ah, I see what you're doing. You are using the OnError and OnTaskFailed handlers for the same task. Let me explain the difference between the two event handlers.

    When a task fails do to an error, the OnError event is fired. That failure event then propagates up the chain in the package. Everything up the chain from the task that failed with fire the OnTaskFailed event.

    This means that your Execute Process Task will fire the OnError event, but not the OnTaskFailed event. If this task is inside of a container, the container will fire the OnTaskFailed event.

    You need to move all of the tasks from your OnTaskFailed handler flow to your OnError handler flow and put them inside of the For Loop Container, before the final script task that you just added.

  • wschampheleer

    SSCertifiable

    Points: 5504

    The following also seems to do the trick (I'm using SQL Server 2008) and is more elegant than the For Loop:

    on the properties of the Event Handler (note that I mean the Event Handler itself, not one of the tasks inside the Event Handler), set ForceExecutionResult to Failure and FailPackageOnFailure to True.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • sql 20683

    Grasshopper

    Points: 11

    Aaron,

    I'm hoping you article will help my issue where no event handlers are firing when a package is restarted from chekpoint file. I am using the OnPreExecute and OnPostExecute event handlers of the package and expect them to fire before and after execute of every task in the package. This works fine for a fresh start, but no event handlers fire after restart.

    First I thought I wouldn't need to follow your workaround because the tasks in my handlers were no set to FailPackageOnFailure. I thought this determined whether the task completion was written to checkfile and in turn whther the task would not run again on restart. I guess I was wrong.

    So now I've wrapped the handler tasks in loop containers and set the properties as in your article...still no love. The handler tasks do not fire again after restart.

    The only thing I can think of which is different from your description, assuming I understand your article, is that I am not using unique handlers per-task. I am using the OnPreExecute of the Package and wanting it to fire on preexec for every task in package. This works fine except for upon restart from checkfile.

    What am I missing?

    TIA,

    Josh

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply