Messaging is one of the most useful features of SSIS.
Actually if you want to make a robust and fault tolerant package you need to design logging on errors and warnings properly. there are some options for send notifications in SSIS; Send Mail Task, Script Task ( scripting exactly any type of message ) and Notify Operator Task.
In this post I’ll show you how to use Notify Operator Task.
Notify Operator Task works with SQL Server Agent Operators.
SQL Server Agent Operators are persons or groups who receive electronic notifications.
So if you want to inform person/group with email, you can define operators in SQL Server Agent.
An important thing to note is that; mails sent to Operators only by Database Mail or SQL Mail defined in SQL Server Agent.
So you need to Enable Mail Profile in SQL Server Agent.
For enabling mail profile, you need to have a mail profile of Database mail or SQL Mail.
So I’ll describe everything from scratch;
First of all let’s create a Database Mail;
Note that I didn’t used SQL Mail because it will be discontinue soon.
1. Open SSMS, Connect to Database Engine.
2. Under Management, double click on Database Mail.
3. In the Select Configuration Task, choose Manage Database Mail Accounts and
Profiles. click Next
4. In the Manage Profiles and Accounts step, choose Create a new account option, and click Next
5. in the New Account step, enter an Account name. then enter E-mail address with a smtp account,display name, Reply e-mail and smtp server name.
in SMTP Authentication, choose Basic authentication, enter username and password.
6. In the next step Finish the wizard.
7. In the SSMS double click on Database Mail under Management again,and choose Manage Database Mail Account and Profile, click Next. In the Manage Profiles and Accounts step, choose Create a new profile
8. In the New Profile step, enter profile name, and add the account you’ve created in previous steps.
9. Finish the Wizard.
there are other ways to setup Database Mail, you can find them here:
http://msdn.microsoft.com/en-us/library/ms188298.aspx
Now we are going to Enable Mail Profile in SQL Server Agent;
10. In the SSMS, right click on SQL Server Agent and select Properties.
Note that SQL Server Agent service should be started.
11. In SQL Server Agent Properties, go to Alert System.
12. In the Mail session, check the Enable mail profile, set Mail system with Database Mail, and choose Mail Profile aslo. and then Click OK.
after enabling mail profile in SQL Server agent, you should restart the service.
So right click on SQL Server Agent and choose Restart.
In this step we will create an Operator;
13. Under SQL Server Agent, right click on Operators folder, and choose New Operator.
14. In the New Operator properties, enter a name, and in the E-mail name write email address of person or group, and then Click OK. note that we don’t use Net send address or Pager e-mail address name because they will be discontinued in next versions of SQL Server.
All requirements for using Notify Mail Task implemented already. Now it’s time to use the Task itself;
15. Create a SSIS Package, and drag and drop Notify Operator Task from Maintenance Plans Tasks into the Control flow.
16. Notify Mail Task is simple to use task, just setup a connection to server. then choose the Operators to notify, enter the subject and body of message.
17. Click on OK, and run the package, then check the Operator’s mail inbox, there will be an email from Database mail’s profile there.
Note that you can pass dynamic Operator/subject/message to Notify Operator Task with help of expressions.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.