James Nix Blog

Error Handling Template

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
@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


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
Result &= Item.Name & " = Nothing" & vbCrLf
End If
End If

Result &= vbCrLf & "Error Mesages:" & vbCrLf & _

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-

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

Legacy Comments

re: Error Handling Template

Davide Mauri
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:


You can have all the log info you need withouth having to modify packages or create packages for that purposes.

re: Error Handling Template
Greetings !

So how do tie up the template with the other packages?


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?

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

Jason Crider
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
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.