Using Zenoss to Monitor SQL Server Integration Services ETL Jobs

Zenoss (Zenoss Core) is an open source application, server and network management platform based on the Zope application server. Recently our team implemented Zenoss to monitor production servers for a client. This gave us the ability to monitor event logs on servers in near real-time and set rule based email alerts. Also it was a great step in the direction of unified alerting and monitoring for all applications. Zenoss is ideal when your ETL enviroment does not have access to SMTP servers.

Design

We have a SSIS package that is triggered every few minutes by a SQL Server Job.When an error occurs in ETL(SSIS), the SQL Server Agent writes an error to the server event log. This event is captured by Zenoss. An alerting rule in Zenoss sends an email to admins that need to be notified.

Setup Alerting Rules

To setup an alerting rule in Zenoss, go to Preferences>Alerting Rules>Create a New Rule.

Monitor Jobs

After the alerting rules are setup lookout for emails from Zenoss. I usually forward the emails to folder that I monitor regularly. After receiving an error alert:

1. Fix the ETL error

2.  Close the event by clicking on the X button - this will tell Zenoss to stop sending you emails for the past error events. A new email will be sent only if the error happens again.

This approach works well. The only drawback is that depending on the Repeat Time on the job you set, you may be bombarded with emails once an error occurs. A easy fix for that is to simply disable the rule once you have received the error notification.