An alert enables an operator or operators to receive notifications when a certain event occurs within SQL Server, such as a query error, SSIS package failure, or backup completion.There are three different methods of notifications, as follows:
- Database Mail
- Pager (for which support will be removed in the future, but is still supported for anyone stuck in 1994)
- Net send (still supported but this feature cannot be used for any OS after Windows XP Service Pack 2)
Hmm, so there’s really only one method of notification which you should use: the Database Mail notification.
There are three ways in which you could use notifications with SSIS packages:
- The Send Mail Task, typically in an Event Handler (SMTP server will be required to set this up)
- The Notify Operator Task, which is similar to the Send Mail Task, but allows you to select the operators in SQL Server Agent to be notified
- The Execute SQL Task, using the sp_notify_operator command, is basically the same thing as the Notify Operator Task, but this method allows greater flexibility.