Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

SSIS Event Handler Variables – Propagate

with 6 comments

The Propagate imagesystem variable in SSIS is used to determine whether an event is propagated to a higher level event handler.  This allows package execution to continue once an event is handled in the container that has generated an Error.  For example, if you have a sequence container that contains an Execute Package Task and a Dataflow Task – but you don’t need the results of the Execute Package Task to stop the container from continuing you can use this variable in the event handler to log the issue and continue on to the Data Flow Task without the package stopping.  This can be handled as well using precedence constraints in your package – but using the Propagate variable allows you to simply log the error (or handle it in any other way fit) using an event handler and continuing on as if nothing happened. There may be times when information is not available at the time of execution, a network error occurs, or some other issue takes place that will not adversely affect the rest of the execution of the package, and can be retrieved at a later execution – the Propagate system variable can help by forcing the package to continue without adding in logic to handle the failure inside the control flow – but instead handle it within the event handler and move to the next task.

This would not be a best practice in many Production or Test situations – because of the unknowns involved with this type of handling – but it can come in handy when developing packages that have some unknowns during the beginning of the development cycle – and later handled properly using precedence constraints when the unknowns have been defined or eliminated.

To find the Propagate variable, select the section you wish to add an event handler to and click the Event Handlers tab within the package designer (Figure 1 – arrow 1).

 image
Figure 1 – Finding the Propagate System variable

Once you are in the Event Handlers section of the package designer, open the variables window (View > Other Windows > Variables) and select the “Show System Variables” button on the top of the Variables window (Figure 1 – arrow 2).  This will show all the system variables related to the event handler.  Scroll through the many variables that have been exposed by selecting the system variables and locate the “Propagate” variable entry (Figure 1 – arrow 3).  This variable is scoped to “OnError” and has a Data Type of Boolean.  Setting the value of this variable to “False” (Figure 1 – arrow 4) tells SSIS not to propagate the error encountered up through the rest of the package chain – and allows the package to continue execution if an error occurs on the section you have defined an Event Handler for.

Important Note

If the package that you have configured with an Event Handler is being executed from a parent package, this will not return a successful execution to the parent.  The Propagate variable is local to the package only – and the outcome of the package is bubbled up to the parent as a failure.  In order to handle the error so that the parent package ignores a failure – you must set the “DisableEventHandlers” property of the Execute Package Task within the parent to “True”. 

Now we are getting into pretty risky territory by setting that property on the Execute Package Task.  If you have unhandled failures within the child package (IE – the propagate variable is set to true and no handlers are defined) – then they will be ignored by the parent package regardless of you handling the issue or not. 

I do not recommend this approach to package development and deployment – this takes too much control away from the packages built in Error reporting and control – but this can be handy when first developing new control flow routines – to find issues within the package, and then fix them using rules and control flow.

Advertisements

Written by Simon Worth

November 11, 2009 at 11:00 am

6 Responses

Subscribe to comments with RSS.

  1. your approach to setting disableeventhandlers on the exec pkg task doesnt work. My mstr’s event handler is firing when errors occur in the sub pkg.

    stan teitelbaum

    June 28, 2013 at 4:42 am

    • Hi Ayyappan, Great post….I’ve tested your steps.Can U pls clarify me further on my below observations about Event Handler behaviour.

      changing the Propagate property value works fine while working with OnError event handler.But while working with OnTaskFailed event handler doesn’t matter whether Event handler level, you set the Propagate property–>False or not,you must have to make the MaxErrorCount property of both the Task and ForLoop set to 0,otherwise the Package execution completely stops on the first iteration itself,causing Task & ForLoop failure.
      I didn’t get any explanation for such behaviour.Pls share your idea why it’s not happening in case of OnTaskFailed event handler.

      Many Thanks,
      Shovan

      Shovan Mukherjee

      December 4, 2013 at 3:40 am

      • One more doubt here.I’ve taken a ExecuteSQL task inside a ForLoop container and tested your way making Propagate property–>False,that absolutely works fine.
        1) But if I take ExecuteSQL task inside the ForLoop container, the OnError event handler fires twice,inspite of once.
        2) Again if I take Script task inside the ForLoop container,the OnError event handler fires only once as per the expected behaviour.

        I didn’t get, Why the Event Handler is firing twice in case of failure of ExecuteSQL task,inside the ForLoop.

        Thanks,
        Shovan

        Shovan Mukherjee

        December 4, 2013 at 3:48 am

  2. […] donc mourir ici. C’est pas très joli et c’est expliqué en détail dans cet article : https://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/ et dans celui-ci […]

  3. In SSIS-2012, this seems to be not working

    Deepak

    November 20, 2015 at 8:30 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: