SSIS error reporting with Event Handlers

When an error occurs in your ETL package, as a data warehouse administrator you also want to know what went wrong and when. When you use an event handler, an e-mail can for example tell you that an error occurred, but not where it went wrong.

This article shows how you can configure an event handler in your master package that mails a report with error details from the start of the last run. I assume that there is a master package that calls all subpackages and that logging is turned on. You need logging to find out when the last run started.

Step 1: Configure the Event Handler
The first thing to do is create an event handler that runs a number of steps the moment something goes wrong. Click the “Event Handlers” tab and choose the level at which the event handler must fire. In the example below, the scope of the event handler is the whole package. You can also configure an event handler on a single task. In the second dropdown box, choose the option “OnTaskFailed”. This makes sure this event handler is called the moment the package fails.

Then drag these tasks onto the screen: data flow task, send mail task and a file system task. Connect them in that order with the precedence constraint (green arrow).

Step 2: Configure the Data-flow task
Next we create a data flow task that gets a dataset for us with information about the error messages. This is useful to find out where the error actually occurred. By default all errors are logged in the sysdtslog90 table.

Double-click the data flow task. You are now in the data flow task editor. Drag an OLEDB Source and a Flat File Destination onto the screen and connect them with the green arrow. Give the two tasks meaningful names right away, such as OLEDBSRC Sysdtslog90 and FFD ErrorLogETL, so other people also see what happens without digging through the code. Your data flow task looks something like this:

Now we must set up the OleDB Source so it gets only the error messages from the last run. Because SSIS logs the start and end times of packages, it also logs the start time of the master package. What we actually want to know is:

- > Give me a list of all error messages from the last run.

So the first thing we want to know is when the last run started. We can find this by looking for the last time the event “PackageStart” occurred for the master package. Then we look at which error messages occurred after that time. You can use the query below for this. The only thing you must change is the name of the package.

-———————————––

1
SELECT ROW_NUMBER() OVER (ORDER BY Starttime ASC) AS FoutNr,
2
source AS Bron,
3
starttime AS Starttijd,
4
[message] AS Foutmelding
5
FROM sysdtslog90 AS S
6
WHERE starttime >= (SELECT TOP 1
7
starttime
8
FROM sysdtslog90
9
WHERE source = 'VUL_HIER_JE_PACKAGENAAM_IN'
10
AND Event = 'PackageStart'
11
ORDER BY starttime desc
12
)
13
AND [event] LIKE '%error%'

-———————————––
Enter the query above when you configure your oledbsource. As OLE DB Connection Manager, choose a connection to the database that holds the sysdtslog90 table, and as Data Access Mode choose “SQL command”.

Now we write the data we got to a text file that we will mail later. Double-click the Flat File Destination you created earlier and configure it as you like. Also create a connection manager for it, for example with a delimited text file, whatever you find easiest.

The data flow task is now ready. It gets a dataset with all error messages and writes them neatly to a text file.

What we still have to do is send an e-mail to the administrator.

Step 3: Configure the E-mail task
Go back to your event handler tab and double-click the Send Mail Task there. Configure all the settings you need: SMTP Server (create it first), From, To, Subject, MessageSource. For attachment, choose the text file that the data flow task created earlier. So this path must match, for example: d:SQLLogErrorLogETL.txt

It may give a warning now, because the text file does not exist yet. When the package runs, this file is generated automatically, so you can ignore this message. Also watch out for McAfee virusscan, which blocks port25 by default. You can turn this off.

Step 4: Clean up the log file
It is tidier to clean up the log file afterwards. Double-click the File System Task. There, choose Operation: Delete File. For SourceConnection, choose the Flatfileconnection you created earlier, which holds the log.

We are now done. Test whether it works by putting an error in somewhere and running the package. For comments or questions, leave a message!