Way back, years now, I sat down with Euan Garden in his office for several days for literally hours and started talking about all the stuff we were going to do with DTS. Euan is a font of knowledge and those few days were like drinking from the proverbial fire hydrant. We talked about how we can make DTS better. We talked about all the problems and pain points people experienced with DTS. How much trouble it was to debug DTS. Loops and connections that transparently synchronize workflow. So many ideas flowed from those initial discussions, it's hard to qualify or quantify their importance in the design of what is now known as Integration Services. One of the problems we discussed was how to handle errors specifically and events generally inside the package. Well, Euan moved on to bigger and OK things. :) The DTS team continued thinking about this problem. We already had a workflow system, why not handle workflow events with workflow. This idea grew into what is now known as event handlers.
Event handlers are one of the lesser known features, but judging from the news groups, people are starting to realize their potential. Event handlers are documented in books online, so I won't give too much background here. But, I do want to point out a few interesting things about Event Handlers and help folks understand them a bit better.
This is the link to the books online topic.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/6f60cf93-35dc-431c-908d-2049c4ab66ba.htm
Event handlers are like small subpackages inside a package. The same could be said about all containers I suppose, but what sets event handlers apart is they only get called when an event is raised by a container or other object in the main package. For example, every task raises the PreExecute and PostExecute events. Even custom tasks that aren't explicitly coded to support events, raise events. Those events bubble up their parent and ancestor hierarchy until they reach the package. Each ancestor, in turn, gets a chance to execute their event handler. If there is no event handler defined, the event just bubbles up to the next parent. If no event handler is defined for the event anywhere in the hierarchy, the event is just returned to the event sink the client application provides. If the client application doesn't provide an event sink, the event is lost. This is how the designer gets all the information that it provides to the user while running a package. For example, when a task posts an Error event, the designer captures that event and shows the error in the progress view and the errors window.
Event handlers are containers just like a sequence or a foreach loop. They may hold other containers and tasks. They may be executed and they may succeed or fail. But, they are only executed in response to an event and aren't part of the regular workflow.
Event handlers created at package scope handle every event of that type for the entire package. An Error event handler placed on the package, will execute for every error event in the package. So, this isn't always the best approach to handling events. You should place event handlers strategically based on the event you wish to handle at the scope you wish to handle it. Even then, you may get unwanted events bubbling up to your event handler. In those cases, it's possible to control event propagation in a couple of ways. One way is to disable the propagation down the chain by setting the System::Propagate system variable on the event handlers of containers/tasks that you don't want to propagate. This is best because it's more efficient and cuts the event off at the source eliminating the fairly costly propagation through the chain. Another way is to filter at the event handler itself. You can do this by placing a script task in the event handler and filtering on the event handlers parameter system variables. Every event handler that handles events that take parameters, will have system variables that contain the values of those parameters. For example, every event handler has these system variables:System::SourceDescription, System::SourceID, and System::SourceName. If you name your containers distinctively you can effectively filter on these values. Specifically, you can use the Description property on all objects for just about anything you wish, since it's reserved for users and IS never uses that property for any purpose. You can group containers by giving them descriptions such as “Handle events: Error;PostExecute“ In your script task, you can then use this description to filter. Again, this isn't terribly efficient, but works in a pinch for some scenarios.
Play with event handlers a bit. They're very powerful. Some of the uses I've seen or heard of include handling errors, sending mail, auditing results, processing log files post execution, pre and post package execution cleanup and setup, pre-charging caches, executing contingent workflow based on variable value changes and so forth. I'd love to hear about any ways you've used event handlers, especially if you've used them to accomplish something that you couldn't have done easily in any other way.
Custom tasks may also create their own custom events. And, what's more, you may create event handlers that execute when those custom events are raised.