asked
Propogate Property on Sql Server Tools(2012)
Well, writing this question after reading a lot of MSDN docs and blogs. My scenario is as below: 1. I have a Execute Process task on the Control flow of one Package. 2. This task calls another package which has a For Each Loop container and Bulk Insert Task in a package. 3. Execute Process task has a Pre Execute , Post Execute and a ON error event handler. 4. I have created my On error event handler to consolidate all error messages and send an email, I get a single mail for this. I want the On_Error event handler to propogate to the parent package. But I don not want the event handlers on the child package to propogate and fire a mail to me as and when it completes. I did try setting the Propogate system variable to false on all of the child packages, but did not work. I even tried by setting DisableEventHandlers on the child package and its executables. This did not work either. MSDN says that at runtime during validation setting Propogate to false will be discarded by SSIS. Is this only on 2012 or is this the same with other versions. How do I stop the propogation of events from child packages without having to do loop on my Main package event handler. AVK
Just thinking with you here... You want the parent package to collect and email all errors after the child package is complete? As the child package encounters errors, can you have the error handler combine the results into a variable that is shared with the parent package? Then, instead of propagating the errors and using an error handler in the parent to send the emails, the parent package can email the variable results when the child package is complete. Alternatively, you could propagate the errors out of the child package and the parent package could aggregate these errors until the child package was complete - sending the error report at that time.
Ken, Yes, I am consolidating all the error messages and then sending an email once the child package completes. However I was trying to set the propogate property on the PRE Execute and Post execute event handlers to False on the child packages so that the I get a single email from the parent. But setting the propogate property to False at the child level does not stop it from being bubbled up to the Parent Package because of which I get a Pre and Post Execute email for all the executables. MSDN says that setting the Propogate property to false will not have any effect during Run time as this will be not be considered. Trying to figure out as to how the propogate property would be useful in trying to stop the events to be propogated to the Parent level in case of other Event Handlers other than On Error. AVK
In this case I would recommend storing the results of OnError in a variable then send the email from a dedicated task rather than triggering the email directly from the event handler. Similar discussion here a year ago where OP took the variable approach:
http://ask.sqlservercentral.com/questions/45563/ssis-error-handling-stop-propogation.html
14 People are following this question.
