How do I performance optimize my N-Tier SSIS Packages for use with the Auditing Framework?


When applying the Auditing Framework to packages that are a part of a Parent-Child relationship (meaning one package uses an Execute Package task to execute the other package), you may notice a slight decrease in performance. This becomes even more noticeable when using an N-Tier pattern for SSIS Packages (Master-Child0-Child1-...-ChildN) where all tiers contain the Auditing Framework. This behavior is due to the way the SSIS Runtime handles events and the way they bubble up to their respected parent package(s).


Another issue that the SSIS Runtime has is that as events are bubbled up the chain, each parent will copy the event and adds it to the list of events that will be bubbled up. This causes an exponential increase in the number of events needing to be handled. For example, a single event at the lowest level will generate eight events at the "Master" level when executing with four levels.


Workaround

How to design your parent package(s) for optimum performance when using the Auditing Framework


1.   Before applying or re-applying the Auditing Framework, wrap each Execute Package task inside of a Sequence Container

2.   For each Execute Package Task, go to the task's Event Handlers tab (not the Package Level Event Handlers) and create an empty event handler for OnPreExecute, OnPostExecute, OnWarning, and OnVariableValueChanged.

Important Note:

You can also elect to create an empty event handler for the OnError event if the user does not wish for the Parent Package(s) to be notified of an error within the child package.



3.   Within each of the empty Execute Package Task's event handlers that were just created in step 2, set the System::Propagate variable to False. This will prevent the event from being broadcast to all parents and logging the event as if the parent package actually generated the event.

4.   Save the packages as well as the project, if applicable.

5.   Apply the Auditing Framework normally to each package.


Advantages

The advantage of this approach is that each package will log exactly what it generates and does not include any Auditing Framework details that belong to one of its child packages. This will also let the user see all the details within the Monitoring Console, even if the package(s) were executed as a subset of the entire chain of packages.


Disadvantages

The disadvantage of this approach is that because System::Propagate is set to False, we are "hiding" the events of each Execute Package Tasks. This will prevent the Monitoring Console from being able to view the Execute Package Task start and end time and will display them as though they were not executed at all. That being said, the Sequence Container will display its status normally. This is a small price to pay for an exponential increase in package performance.