Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Event Handlers with Checkpoints (Part 3) Expand / Collapse
Author
Message
Posted Tuesday, March 10, 2009 12:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
Comments posted to this topic are about the item Using Event Handlers with Checkpoints (Part 3)

Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #672187
Posted Wednesday, March 11, 2009 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:35 PM
Points: 17, Visits: 192
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.
Post #673027
Posted Wednesday, March 11, 2009 6:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
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.


Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #673198
Posted Thursday, March 12, 2009 1:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:35 PM
Points: 17, Visits: 192
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.





Post #673972
Posted Thursday, March 12, 2009 7:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
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.


Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #674213
Posted Monday, January 25, 2010 6:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 2:02 AM
Points: 846, Visits: 303
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
Post #852969
Posted Wednesday, September 01, 2010 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 15, 2011 9:50 AM
Points: 1, Visits: 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
Post #979121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse