Monday, July 28, 2008

Package Failure Notification

One way to guarantee disagreement among a room of ETL developers is to begin a discussion on how to notify when a package fails - and that argumentative topic is the subject of our discussion today.

When I'm working with clients designing ETL jobs one of the first questions I ask from my fact finding checklist is "What is the package failure notification requirements?". This answer not only varies by client but also by team members within the same organization. This simple question often has to be escalated up to the senior manager to get the correct answer. Developers often want to be notified by email, preferably next-day notification while team leaders and managers want people notified by pager or phone calls when the package is a critical data task. I've also found developers on the same team desire next day email and immediate phone call from the operations control even at 2 AM.

There has to be a middle ground in notifications. When business critical metrics are at risk of not being met, immediate notification is a reasonable requirement. If no one will be looking at the data until Monday and it's Wednesday, an email should be sufficient. Thankfully, SSIS has a Send Mail task that can be used to generate emails for failures or abnormal operation. A good example of using Send Mail task is when a process runs but only processes 10,000 records when the daily average is 100,000. In this case, a proactive email can be sent to the developers to do some research before the business metric is published wrong and senior management notices. DataStage 7.x also has notification capabilities when using the Sequencer and a notification task.

The decision on notification activities should be as consensus as possible among an organization and thankfully the major ETL players in the market provide easy development of notification processes in ETL jobs.

Tomorrow we will do a deeper dive into yesterday's topic of splitting up ETL jobs so stay tuned.

No comments: