Notify Operator Task – SSIS

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply