SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Sending SSIS Errors to the Windows Event Log

 In a previous post I explained how to trap for errors in your Script Task and Components and send the information to the SSIS Log (link).  A good friend of mine and system engineer extraordinaire Eugene L. (last name withheld to protect the guilty :) ) suggested that I also push these errors to the windows event log.  We have HP OpenView which is monitored by our operations group, which will enable us to setup alerts through them as well.  This sounded simple enough and after about 5 min of research it turns out it is simple.  All you need to do is add an additional class and one additional object.

First you need to add, using System.Diagnostics.  This provides the hook to the classes that will enable you to push your error to the Windows Event Log.  Now create the object to interact with it:

EventLog evLog = new EventLog("Application", System.Environment.MachineName, "ImprovedTidalExtract");

I set up the header for the log.  This is an "application", being run on "System.Environment.MachineName", and the application name is "ImprovedTidalExtract".

Now I have to provide the detail information, that is the actual error.  Within the Catch block, I add an additional line.



How will this look within the event log?  Like this:



 The detailed entry I created with the above try/catch looks like this:



This blog is syndicated from SSIS - SQL Server Tidbits(http://www.josefrichberg.com/)


Posted by Steve Jones on 1 February 2010

That's a good thing to know. Could be very handy to have these errors logged to Windows so that some monitoring application can pick them up.

Posted by VALEK on 9 February 2010

Very cool! There should be a component in SSIS doing just this.


Posted by niall.baird on 9 February 2010

We also use HP Openview, and I've built a package which I put into the event handler (onError), which calls the sd_Event executable to automatically raise an incident in OVSD.  While I can't provide the package, basically I trap the error in the event handler, the first part of the routine puts all details into the sql server database and adds some extra info (for OVSD), then calls the package to raise the incident.  This package retrieves the metadata & error data from sql server, puts it into a recordset destination which is matched to a variable.  Next, there is a script task which manipulates the error data into a 'human readable' string containing all the parameters required by sd_event, finally I call an 'execute process' task which calls sd_event.exe with the parameter string.

It worked in anger for the first time last night!

Leave a Comment

Please register or log in to leave a comment.