SSIS Script Task Custom Events

So, an interesting observation regarding SSIS 2008, or should I say anoyance?

At the moment I am part of a team building a data warehouse for an investment bank. Part of this system uses SSIS 2008 (or DTS100 in object model speak) in order to load batches at regular intervals into the warehouse. To ease the monitoring and issue resolution aspects of systems of this nature, we are aiming to use NetAgent to poll the database for new events of interest. Events such as load starts, failures, completion and dimension and partition processing activities all need to trigger an email or something to notify support of the system state.

Well, now you might think, like me, that rather than just polling for "PackageStart" events or any of the other "standard" events we could create our own events that could be used to trigger alerts. Yup, now normally I'd say good idea, but alas this time I can't, I don't think...

See, now in SSIS you could add a script task and using either C# or, shudder, VB .net, use Dts.Events.FireCustomEvent, or a derivation thereof, depending on the language chosen, to fire an event. Using this approach you could quite nicely craft your own custom events, giving you the ability to uniqely name this event. (I would presume that doing this would provide a unique name in the [source] column in the [sysssislog] table such that the event would now be uniquely identifiable, though documentation around all of this appears sparse, at best). One snag though...

Now before I get to the proposed gotcha I guess I should 'fess up... I've only been using SSIS in any guise for about 9 months, so by no means am I a seasoned veteran of the product. Thus quite possibly there is a solution to this gotcha. Just maybe not an intuative one? (Hmmmm speaking of intuative, just don't get me started on case sensitive lookups. I know that they may well be quicker but really? WTF?)

Anyhoo, so now that you have your über cool custom event, here's the issue. How do you get the SSIS package to log it? If you edit the logging properties of the package, even specifically selecting the script task, you are only presented with the predefined events for logging. You may be able to "load" the custom events or perhaps edit the package XML but really?

So long story short, if someone knows of a way to get an SSIS package to log script task defined custom events, please don't be shy and let me know...



- Posted using BlogPress from my iPhone

Location:Somewhere on South West Trains