Geeks With Blogs
Jim Giercyk

In a complex SSIS Control Flow, there is often a need to have a common last step, regardless of the number of Control Flow end points.  Consider the following example:

image

In this case, we are using the Completion constraint (blue line), and an Expression to determine which path to follow.  If there are records, we upload them, if not we end without doing anything.  In either case, we are writing a message to a log table, recording the event.  As simple as this example is, we have 2 end points.  Now, what if we wanted to insert a record in the log that records the time the package ended?  In this scenario, we would need to code 2 additional INSERTs to the event log, one for each end point.  I am a firm believe in the theory that “the less moving parts the better”.  Let’s create an INSERT statement that will be executed regardless of the end point in the Control Flow.  ENTER THE EVENT HANDLER.

Let’s add a OnPostExecute event handler to at the Package level.  This will be hit when the package finishes, and we can record the Package End event.

image

Sounds like a great idea, and it’s true that the event handler will trigger on the completion of the package.  The problem we face is that events “bubble up” the event tree:  http://msdn.microsoft.com/en-us/library/ms140223.aspx.  That simply means that the OnPostExecute event will trigger for EVERY task in the package.  This is very useful, but it does not help us in the case of recording a Package End event.  Here is what our log looks like if we use a OnPostExecute without any constraints or expressions:

image

This is not exactly what we were going for.  So, how do we make SSIS understand that we only want an event written to the log if it is the last event in the package.  We could set the DisableEventHandles property to TRUE on every task in the package, but odds are pretty good you will want to track other events besides a Package End event.  Here is a modest solution:

image

By adding a Dummy Task, we can use expressions and constraints to create a Control Flow within the Event Handler. The Dummy Task in this case is simply an Execute SQL Statement:

image

The Expression used to pick the last event is this:

image

The @SourceName is a system variable that contains the name of the task that triggered the Event Handler.  The @PackageName is also a system variable which contains the name of the package.  The package is a task itself….it is a container holding all other tasks, and will always be the last OnPostExecute event.  When we run the package now, the results look a little more like what we would expect.

image

This gives us the power to do ANY processing after ANY task in ANY Event Handler by adding a constraint and expression to the Dummy Task.  Event Handlers are extremely useful in creating log files, but they are also great for consolidating duplicate “at end” processing when there are multiple end points, and reducing duplicate processing within parallel data flows. Have fun with it, but beware of the Event Tree!! 

Posted on Friday, April 12, 2013 11:02 AM | Back to top


Comments on this post: Adding a Control Flow to SSIS Event Handler

# re: Adding a Control Flow to SSIS Event Handler
Requesting Gravatar...
The event tree is killing me. Working with VS2010.
The solution you propose is exactly the first thing I came up with. The problem is that your dummy task is a task so it will also raise events.
My log is far from done... any thoughts on that?
Left by Glen on Jun 05, 2015 5:29 PM

# re: Adding a Control Flow to SSIS Event Handler
Requesting Gravatar...
The dummy task will not raise events unless you specifically code them. The trick is adding the constraint to fire only when the task is the package name. If an event from any other task in the package manages to bubble up to the package event handler level, the non-dummy task will not fire, because it does not meet the constraint criteria. Putting a constraint and dummy task on every event handler you want to trigger will keep other tasks from firing when they bubble up. I'm not sure if that helps, but the importance is the constraint - it allows you to control when you want the event to fire no matter how many times it gets hit.
Left by Jim on Jun 11, 2015 10:05 AM

# re: Adding a Control Flow to SSIS Event Handler
Requesting Gravatar...
I have checked the same info and it found to be effective. Recording portion was little bit difficult but the rest of things are really fantastic. Thanks for sharing this article with lots of things to learn and keep your great work.
high speed cable internet
Left by Merlin John on Jan 04, 2018 7:04 AM

# play
Requesting Gravatar...
Play the most popular free online card forever here the amazing site for the players you play spider solitaire free online can create the best score to select the card online best so have the best fun forever thanks for the visit here.
Left by game on Apr 16, 2018 12:36 PM

Your comment:
 (will show your gravatar)


Copyright © Jim Giercyk | Powered by: GeeksWithBlogs.net