Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLRunner

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

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.

img1

 

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

img2

 

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

img3

Comments

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.

Thanks

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.