January 28, 2011 at 5:18 am
hi All,
I was testing error handling in ssis with a mickey mouse package
1) in the events tab on error handler for the whole package created a very simple script task that pops up message
"error handler triggered"
2) Created in control flow a very simple script task that I force to fail with DTS Task result on failure
and then set in properties "SetFailPackageOnFailure" to true
3) This i ran and as expected the the error handler was triggered...so far so good
i then set the properties back to "SetFailPackageOnFailure" to false on the script task
and then in the control flow created a another script task that pops up a message and set a Failure constraint
(red line) ) to this from the original script task (that has now been set to "SetFailPackageOnFailure" = false). This
i ran and my expectation was that the second script task would run ignoring the error handler. However the package errorhandler still
gets triggered
how come ? the "SetFailPackageOnFailure" has been set to false, so shouldn't the on error handler be ignored
and the failure constaint red line be followed to the second script task ?
how do i fix it to do this ?
January 28, 2011 at 6:09 am
If I'm following you correctly, you have a task that fails (on purpose), and it has an on-failure path from it, which isn't being called when it fails. Is that correct?
What is it doing instead?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2011 at 6:06 am
robinrai3 (1/28/2011)
...i then set the properties back to "SetFailPackageOnFailure" to false on the script task
and then in the control flow created a another script task that pops up a message and set a Failure constraint
(red line) ) to this from the original script task (that has now been set to "SetFailPackageOnFailure" = false). This
i ran and my expectation was that the second script task would run ignoring the error handler. However the package errorhandler still
gets triggered
how come ? the "SetFailPackageOnFailure" has been set to false, so shouldn't the on error handler be ignored
and the failure constaint red line be followed to the second script task ?
how do i fix it to do this ?
First of all, what is a micky mouse package? (I'm curious :-))
Regarding your question:
if I'm not mistaken, the OnError event of your Script Task is propagated to the package level, so the event handler is still fired.
The following BOL page explains the concept:
http://msdn.microsoft.com/en-us/library/ms140223.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 17, 2011 at 2:41 am
hi Koen/Gsquared
Sorry for not getting back to you...been away...first of all a "mickey mouse" package is just a test harness created to test out a concept....
The "on error" at the package level is the catch all handler to which is triggered when any unhandled error occurs. Usually in this i fly of an email to all and sundry...this kiddie has failed and go check it out
What i wanted to do was to handle the error if a particular task failed but NOT to fail the package entirely and not to trigger the the "on error" at the package level. Reading the link u provided i did what it said was to create an "on error" handler at the task level, which did the handle the error but it still bubbled up the error to the "on error" handler at the package level..not what i wanted.
However after a load of digging around...apparently there is a property that only appears in the system variables in the task "on error" handler called propogate which by default is "true", setting this to "false" stops the error being bubbled up to the package "on error". Therefore my task which fails now goes down the failure constraint which i handle and then carry on.
Hope this helps...
February 17, 2011 at 3:03 am
robinrai3 (2/17/2011)
However after a load of digging around...apparently there is a property that only appears in the system variables in the task "on error" handler called propogate which by default is "true", setting this to "false" stops the error being bubbled up to the package "on error". Therefore my task which fails now goes down the failure constraint which i handle and then carry on.
Allright, that's very interesting. Thanks for posting back!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 17, 2011 at 7:52 pm
Or - you could set the task property "DisableEventHandlers" to true (defaults to false)
February 21, 2011 at 2:30 am
Hi,
When you say you are creating event handler at Package level that means it will be propagate down to child events when the events fires. It doesn't depends on the property FailPackageOnFailure as you think it is.
If you really dont want your task to not participate in event handling activity then you need to set DisableEventHandler=True (for pakg in your case). Doing so will let tasks not to participate in triggering events.
So in your case events will get triggered each time there is an error inside the package (any task, doesn't matter). Thats why your event handler gets executed everytime you are running the package.
Let me know if it helps !!!
Thanks
Anjan
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
February 21, 2011 at 2:33 am
anjanwahwar (2/21/2011)
Hi,When you say you are creating event handler at Package level that means it will be propagate down to child events when the events fires. It doesn't depends on the property FailPackageOnFailure as you think it is.
If you really dont want your task to not participate in event handling activity then you need to set DisableEventHandler=True (for pakg in your case). Doing so will let tasks not to participate in triggering events.
So in your case events will get triggered each time there is an error inside the package (any task, doesn't matter). Thats why your event handler gets executed everytime you are running the package.
niall.baird already suggested the DisableEventHander option a small month ago, and the original poster also found another option, setting the system variable propagate to false, to stop the propagation you described.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply