locked
Stop Event Handler from firing an event RRS feed

  • Question

  • G'day All, I'm hoping someone can save my last tufts of hair... I've tried searching but getting all the standard event handler issues and no idea how I can refine further.

    We have a package level OnPreExecute event handler which does general logging as well as reset various row count variables. For the most part, all well and good but have hit a scenario where I don't want it to fire. I figured the best way to handle this was to make a task level OnPreExecute against the appropriate task and turn off Propagation. As far as I can tell, that works, however, what I have found is that the task within the task-level OnPreExecute is firing an OnPreExecute event which calls the package-level OnPreExecute, any idea how to prevent an event handler from firing events?

    Note, setting the "Disable Event Handlers" on the task within the event doesn't work... I can only guess that disables event handlers defined for the task only, not any parent event handlers...

    Thanks in Advance

    Ryan

    Tuesday, February 11, 2014 1:40 AM

Answers

All replies

  • What is the condition that you don't want it to fire?

    One thing to consider is putting a sequence container at the beginning of your OnPreExecute event and have a precedence constraint that uses an expression.  You put in the expression the conditions under which you want the event to fire.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, February 11, 2014 1:55 AM
  • What is the condition that you don't want it to fire?

    One thing to consider is putting a sequence container at the beginning of your OnPreExecute event and have a precedence constraint that uses an expression.  You put in the expression the conditions under which you want the event to fire.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    No condition as such, generally we take data from databases but we handle a couple of files for which we want to record information returned from the preceding DFT, it is these tasks that we want to stop the handler on. I could add a "No Handler" style tag to its name and only run the handler when the tag is not present but was looking for something a bit more elegant.

    Thanks

    Tuesday, February 11, 2014 2:10 AM
  • You would need to get the No Handler information from the file name and if that information is not there, then execute the rest of the event handler.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Mike Yin Wednesday, February 12, 2014 5:32 PM
    Tuesday, February 11, 2014 2:12 AM
  • Groan... but thanks...

    Another thought I did have, is it possible to actually get the task object that initiated the event? I know we have the TaskID/TaskName, do we have the object itself available within the event? Or can we obtain it from either of these variables? And on the assumption that we can obtain the object, can we then query the properties of the object? So for example, if I set the "DisableEventHandlers" property to false on the task I want disabled, then obtain that task within my event handler script and if the "DisableEventHandlers" is false, I don't run the event handler... in essence, creating a true "DisableEventHandlers"

    Should point out I'm still fairly new to SSIS so may be asking something that's obvious...

    Tuesday, February 11, 2014 2:23 AM
  • There are two System variables available with the events:  SourceID, SourceName.

    Also, check out Propogage as well. http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Mike Yin Wednesday, February 12, 2014 5:32 PM
    • Marked as answer by RyanAB Friday, February 14, 2014 2:53 AM
    Tuesday, February 11, 2014 10:48 AM
  • There are two System variables available with the events:  SourceID, SourceName.

    Also, check out Propogage as well. http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    It got me thinking enough to get a workaround... By adding a tag to the Task's name and then querying this within the package level (as the SourceName field), I could prevent the package level event handler from processing when required... however, that only worked while the SourceName is the lower level task, the event still propagated to the package (Note, here I am saying the package level event handler first ran as the Task and then again as the Package, no task level event handler implemented) so I had to set the Propagation variable to False to stop the handler running at the package level.

    I have to say it's the most unintuitive implementation of event handlers I've ever had to work with... why would we ever want the one handler running a "pre-execute" at the package level when it is just prior to a task executing? By having my handler at the package level, I am expecting the propagation to filter up to the package to be handled, not repeat itself for every container it meets between my task and where the handler is defined. Also, if I add a pre-execute handler at the task level, that should be able to override the event handler at any parent level not be in addition to... and certainly shouldn't raise events itself, event handlers raising events just seems so obsure

    Friday, February 14, 2014 3:06 AM