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.
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
Declare @Result varchar(8000)
Select @Result = Coalesce(@Result, '') + '>>' + message + Char(10) + Char(13)
Where event = 'OnError' And executionid = @ExecutionID
Order By id
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
Result &= vbCrLf & "Error Mesages:" & vbCrLf & _
Dts.Variables("ErrorMessageText").Value = Result
Dts.TaskResult = Dts.Results.Success
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 . 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!!".
 http://support.microsoft.com/kb/908018 Accessed 4/30/2008