SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Event Handlers with Checkpoints (Part 3)


Using Event Handlers with Checkpoints (Part 3)

Author
Message
Aaron Akin
Aaron Akin
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 150
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
prasanna-750812
prasanna-750812
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 200
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
Aaron Akin
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 150
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
prasanna-750812
prasanna-750812
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 200
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
Aaron Akin
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 150
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
wschampheleer
wschampheleer
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 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
sql 20683
sql 20683
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search