By: Matt Wollner
Sr. BI Consultant
In all of the SSIS packages that we develop, we include an auditing and logging framework. This framework consists of adding Event Handlers to the package, such as OnError, OnPreExecution and OnPostExecution.
Furthermore, in many of our solutions we have a Master Package that calls 2nd tier master packages. This results in our solutions consisting of 3 layers of package executions.
When executing the 3rd layer of packages, we have noticed that there is about a 20-30 second overhead for every package. Packages that normally run in sub 3 seconds take 20-30 seconds instead in this scenario.
So, after some experimentation, here’s what we discovered: the OnPreExecution and OnPostExecution tasks are designed to run for every Control Flow Task in a package. When a child package is called, the OnPre and OnPost Executions are called for every Control Flow task in the child package, along with every Event Handler task in the child package.
We’ll illustrate this further with an example of our SSIS framework.
There is an OnBegin and OnEnd SQL task in every SSIS package.
Here are the configures for Event Handlers.
When you execute a Single Package, the OnPre and OnPost Executions are called for each of the 3 Tasks.
If you call this package from a parent package, the OnPre and OnPost Executions are called for the 3 tasks in the child package, plus every task in every event handler is called. If we look at solely the Execute package task and the child package’s SQL HandleOnBeginEvent Task, the Child Package will issue 1 OnPreExecution and 1 OnPostExecution. The parent package will issue 8 OnPreExecution and 8 OnPostExecution
If we take this one step further and have a grandparent package call a parent package, and then have that parent package call a child package, the grandparent package will end up firing 17 OnPreExecution and 17 OnPostExecution for the single Execute Package Task. Each of these tasks takes milliseconds to run, but with so many tasks, these millseconds start to add up, causing the slowdown.
So this begs the question: How can we avoid this?
And here’s how.
For every Execute Package Task that we create, we need to set the Propagate = False for every Event Handler, except for the OnError event. We do want the OnError to bubble up the chain and force an error at the parent and grandparent levels.
Steps:
1. Open the Event handler and navigate to the Execute Package Task.
2. For every event handler defined in your package, Except for OnError, add a blank event handler.
3. While in the event handler, set the System Variable Propagate = False
If you are implementing Event Handlers and calling packages as a Child Package task, make sure you are aware that many events may be propagated to the parent package.
This will become more evident if you are nesting multiple levels of package executions. Updating the Propagate property can cut out a lot of unnecessary overhead.