March 17, 2011
I've been doing a lot of work lately using SSIS to load our production databases. We're loading very large databases and our SSIS packages are long running. Many take days not hours. It can be difficult to understand what parts of the packages are taking most of the time and when or if they have issues. SSIS provides a logging feature that you can enable and it goes a long way to solving those issues. We have enabled our packages for logging and direct the output to a centralized database where we log all of our loading package executions. From there I've written a web application that analyzes the logging database so we can view all the information generated by our packages. I even have a nifty little timeline viewer I've written that depicts our package flow, its simple, but it does the job.
The report has a ton more detail than the timeline, but I'll save that for another day. What i wanted to focus on here was how to generate alerts. We wanted a way to get notifications when packages had issues. Yes, we could do things in the packages themselves to set paths to go to email tasks on failures, but it just complicated the packages and we would have to configure it in each package for each task, you see where this is going. Since we have all of our packages logging centrally we thought it would be easier to setup a trigger on the logging table to alert us when particular events occurred. This ended up working really well once we worked through a few minor issues.
First you have to go through and setup database mail on the server with the logging database which is done by just following through the wizard. If anyone posts any questions I'll go back and provide some more information on how its done.
With database mail setup you have to make sure that the credentials you are using to write your log entries to the database have permissions to send email using database mail. The script to do that is straight forward:
CREATE USER [<ACCOUNT NAME HERE>] FOR LOGIN [<ACCOUNT NAME HERE>]
EXEC sp_addrolemember N'DatabaseMailUserRole', N'<ACCOUNT NAME HERE>'
Now that your account has the right permissions you just need to setup a trigger to send the email. You can customize it anyway to provide different levels of notification based on what you capture in your log table. In the following example we send an email when a package encounters an error.
CREATE TRIGGER [dbo].[SSISError] ON [dbo].[sysssislog]
IF (SELECT COUNT(*) FROM inserted WHERE [event] = 'OnError') = 1
@profile_name = 'Default Email Profile',
@recipients = 'firstname.lastname@example.org',
@body_format = 'HTML',
@body = 'Go check your error.,
@subject = 'SSIS Error'
And there you have it. Logging, reporting and alerts from your SSIS packages. Now go load some data!