Update: Daniel has posted a detailed comment to this below!
Those of you who read Daniel Read's blog will have read (3 different uses of the character sequence r-e-a-d in the space of 7 words - amazing) his excellent post today regarding eventhandlers in SSIS - http://www.developerdotstar.com/community/node/334.
Daniel raised a few points which I was going to leave open to conjecture but I'm afraid I just can't help myself!!
First of all its worth emphasizing what Daniel is talking about here because it cannot be underestimated. He has realised that the eventhandlers provide a fantastic mechanism for extending the inherent SSIS log providers. Sure, you can write your own custom log providers but more and more I'm coming to think that you simply don't have to - eventhandlers are a more than satisfactory substitute, but with an added bonus. The reason that eventhandlers can be substituted for log providers is that they basically do the same thing. They catch events thrown by the package. All the things that get logged by the log providers are simply events thrown up by the package's container hierarchy - in some ways a log provider is just a different implementatiton of an eventhandler. So in essence if you use the eventhandlers you're not doing anything different than the log providers - its just a heck of alot easier to customise. Daniel terms this customisation "domain specific logging" and I think that is a good description.
He also has a great idea to link his domain specific logging with SSIS's own log providers by providing the ExecutionInstanceGUID in his domain specific logging to make it easy to tie back to the SSIS log provider. If you are using the SSIS log provider for text files then you could even include the ExecutionInstanceGUID in the name of your log file and therefore generate a new log file for each execution - and that is very easy to do.
Daniel states he doesn't like the fact that eventhandlers don't interrupt the flow of execution. I can see what he means however I'm happy that it does not do this. The flow of execution can be controlled using OnFailure precedence constraints and that is a better fit to the TRY...CATCH block analogy that he speaks of. Perhaps my lack of a development background serves me well here!!
Cannot debug script tasks in event handlers - I noticed this myself yesterday and just assumed that it was something I had done wrong so didn't pursue it; so its heartening to know that someone else has had the same problem. Let's hope this gets fixed soon.
Events fire too often - Daniel complains that an eventhandler will catch events thrown by the container that it is scoped to AND all descendant containers of that container. I can see why this would be a bad thing but on the other hand it can also be a godsend - it means you only have to apply a generic logging mechanism in one place rather than throughout the package (or all packages - more on this later). Daniel has used a very good workaround to stop eventhandlers catching events raised by child containers that involves the System::SourceName and System::PackageName variables and indeed I have employed this logic in alot of my packages in order to only execute a task in an eventhandler if the event was raised by the package. I prefer to use System::SourceID and System::PackageID admittedly but its basically the same thing and you must be aware of the inherent problem in doing this - Daniel mentions the problem in his post.
There is another workaround however. All eventhandlers have a variable called System::Propogate scoped to them. If you set this variable to FALSE in an eventhandler scoped to the child container then events of that particular type will not be handled by an eventhandler scoped to an ancestral container. Did that gibberish make any sense at all? Probably not.
One other minor thing to note is that Daniel mentions having to put an empty Script Task into an eventhandler in order to conditionally execute a task that does some work. This is a bit of an irritation I have to admit however common convention is to use an empty Sequence Container to do this rather than Script Task.
Daniel talks about a problem with DisableEventhandlers property not having any effect for eventhandlers on ancestral containers. Well I actually think it makes sense that this is the case. The DisableEventHandlers property on a container stops the eventhandlers scoped to that container from executing. It does NOT stop the event from being thrown however and it does not stop eventhandlers scoped to other containers from executing and THAT is why eventhandlers of ancestral containers will still execute. Perhaps this could be fixed using System::Propogate however I do not know if setting DisableEventhandlers=TRUE will also disable the effect of System::Propogate. I suspect not but frankly its too late in the day and I'm too tired to go and investigate it now!!!
So on that note, its time I called it a night. One thing that I will say though is something that I've been meaning to state for few weeks now - and state it much more clearly than I'm about to. If you have a multi-package solution then I strongly recommend you consider implementing it in such a way that all those packages are called from a single "master" package which contains all your custom logging functionality. I won't elaborate on why for now - but think it over!!
All that remains to do is thank Daniel for a really thought provoking post - you really should have a read of it because he explores some great SSIS concepts.
And with that I shall bid you adieu! I was going to blog about File System versus SQL Server deployments this evening but that really isn't very interesting and therefore can wait until another day!
-Jamie