Error Handling Template

Wed Apr 30, 2008 by James Nix

You can expect:

This is a medium intensity post (you should not get a migraine, but I make no promises).

I Expect (that you have the following experience):

Error Logging experience
UDF (User Defined Function) creation experience

Creating a template Package that handles errors.
(also how to create a template and make it available)

SSIS can be the wild west. Each developer doing things their own way. Errors are often overlooked and then when one happens no one knows why or how to fix it. Logging is often not sufficiently enabled. This causes maintenance headaches (especially with Excel, but that is a rabbit trail which dives steeply into the lowest reaches of Hell itself!). What I am going to present with this post is how to log, compile and disseminate error information and not have your package just fail with no idea why.

Logging, GO!

The first thing we must do is create a new package (duh!). Next we turn error logging on. I've shown this before and here it is again (right click on the Control Flow canvas and go to Logging...):

And then set the logging details:

Handle That Event!

Next, we need to handle the error. We could handle the OnError event, but that would fire on EVERY error that was generated. A Task can generate an infinite (well, you get the idea) number of errors, so we don't want to fire an infinite number of error handlers! What we really want to know is when a task fails. We do this by setting up an event handler for the OnTaskFailed event (duh!):

After we create the event handler, we want to create a place to store the error information we are about to gather, so we create a variable. I named mine ErrorMessageText. Here is where things get fun. We want to get all of the error information from the logging we've set up. First thing we need to do is to set up some SQL to get it. You will want to create a UDF for this because otherwise we are in the maintenance nightmare again. Here is a sample that you can use:

Create Function GetErrors(@ExecutionID varchar(50))
Returns varchar(8000) As
Begin
	Declare @Result varchar(8000)

	Select @Result = Coalesce(@Result, '') + '>>' + message + Char(10) + Char(13) 
	From sysdtslog90
	Where event = 'OnError' And executionid = @ExecutionID
	Order By id

	Return @result

End

Now that we have an easy way to collect the error information (notice that I am passing in the execution id and retrieving all the errors for that execution) we need to get the data out of the database and into our message variable. Grab an Execute SQL Task and configure it like so:

If you configure it just like this, it will work. Note that the Execute SQL Task is VERY touchy about this kind of configuration and demands it be configured just so.

Next, we are going to drop in a Script Task to grab the rest of the information we want to present. In the ReadOnlyVariables put: ErrorMessageText, CreationDate, CreatorComputerName, CreatorName, ExecutionInstanceGUID, InteractiveMode, MachineName, OfflineMode, PackageID, PackageName, StartTime, UserName, VersionBuild, VersionGUID, VersionMajor, VersionMinor, ExecutionValue, SourceDescription, SourceID, SourceName

This allows us to produce as much information as possible for debugging. Design the script and use something like the following for your Sub Main:

Public Sub Main()
        Dim Result As String = "System Variables:" & vbCrLf
        
        For Each Item As Variable In Dts.Variables
            If Item.Name <> "ErrorMessageText" Then
                If Not Item.Value Is Nothing Then
                    Result &= Item.Name & " = " & Item.Value.ToString() & vbCrLf
                Else
                    Result &= Item.Name & " = Nothing" & vbCrLf
                End If
            End If
        Next

        Result &= vbCrLf & "Error Mesages:" & vbCrLf & _
            Dts.Variables("ErrorMessageText").Value.ToString()

        Dts.Variables("ErrorMessageText").Value = Result

        Dts.TaskResult = Dts.Results.Success
End Sub

This will collect all the information we need for our error report. We'll then email this to the appropriate person. This ought to be configured in your package with the configuration in a central location. I am not covering configuration in this session, however.

Templates, Templates, Templates!!!

The next part is really hard. No, really.... Put the template package in your drive:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems folder [1]. Done. Now you can right click on your project in the Solution Explorer, go to Add, New Item... and pick the template.

Now everyone uses the same error notification system and you have all the useful information you need when someone decides to change the file format of a data feed that you've been receiving for years without telling you! You can now proudly go to your boss and tell them "It's not my code! Blame the customer!!".

-Madman Out-

References:
[1] http://support.microsoft.com/kb/908018 Accessed 4/30/2008

Legacy Comments


Mladen 2008-04-30	re: Error Handling Template sweet!!

Davide Mauri 2008-07-18	re: Error Handling Template If you need all that logging information (and surely I need it!), I'd prefer not to have to make the package in a way it can produce it, but I would prefere that the execution engine can have logging switched on or off by an option. Thats why at the end a rewrote DTExec, adding integrated logging support: http://www.codeplex.com/DTLoggedExec You can have all the log info you need withouth having to modify packages or create packages for that purposes.

Jojo 2009-01-29	re: Error Handling Template Greetings ! So how do tie up the template with the other packages? Thanks!

Dwaine 2009-06-05	re: Error Handling Template I like your approach. One thing I've come to realize, and am very disappointed about, is that the script task no longer has full access to the package object model at run-time. Is there a way to add to ErrorMessageText ALL of the user defined variables with dynamic runtime code?

DS 2009-11-03	re: Error Handling Template The ExecutionInstanceGUID should be of type GUID and length -1

Jason Crider 2010-02-24	re: Error Handling Template I brought this into 2008 and got the following: Warning at Script Task [Script Task]: Found SQL Server Integration Services 2005 Script Task "ScriptTask_01877badc37046faa8174ed0daaeef0c" that requires migration! There's nothing in that default script that should be a problem in SSIS 2008 right?

Jason Crider 2010-02-24	re: Error Handling Template Edited to add: Got it working in 2008. The function has to be changed to reflect the new table name of sysssislog.