In today’s technological world, we’ve been programmed to expect to be alerted by the technology we’re using whenever we incur an error. However, errors and error messages are not always immediate or obvious. In fact, some error messages are very generic and identifying the issue may take time and effort. In today’s blog, we’ll walk through one such example.
Our scenario involves working with Dynamics 365 and SSIS. When using SSIS with Dynamics, one of the ways to set up an SSIS job is through the SQL Server Agent, a Microsoft SQL server relational database system (RDBMS) tool that allows database administrators (DBAs) to schedule executable jobs in an automated fashion, as well as, manage backups and other similar tasks. In our scenario, the database configuration was complete, operators were set up, and everything seemed to be configured correctly. Indeed, everything appeared to be right with the world… until we attempted to use the new profile to send emails in one of our jobs. What had we done? We set up a new test notification and selected Send Test E-Mail.
Since the email submitted without an error, we assumed it processed successfully. We checked to see if the email delivered. Nothing. We waited, still nothing. Despite the appearance of successfully sending, no email was received. So, the best thing to do next was to check the job history/log file summary to see if there was a record of the email send. With the log file open, we checked the message column and saw, “The job succeeded. The Job was invoked by User…” This was followed by the job status notation and the error message: NOTE: Failed to notify ‘myoperatorname’ via email. (In this example, ‘myoperatorname’ refers to the name we had set up in the operator steps.)
With the log file open, we checked the message column and saw, “The job succeeded. The Job was invoked by User…” This was followed by job status and the error message: NOTE: Failed to notify ‘myoperatorname’ via email. (In this example, ‘myoperatorname’ refers to the name we had set up in the operator steps.)
What went wrong? Why did we see this error? What does it mean and how can we resolve it? Let’s start with what went wrong. When installing SQL server and the SQL server agent, the default setting, “Enable mail profile” should be checked, it wasn’t. As a result, the mail profile was not enabled and even if the setup and test mails are configured correctly, the jobs themselves will not actually deliver the email. What does it mean? We saw this error because the log reflects what occurred and the status. This common SQL Server Integration Services (SSIS)-related error can be troublesome and may cost valuable time to troubleshoot and resolve.” While SSIS and SQL are outside of the immediate scope of Dynamics, it is leveraged for process that affect both online and on-premise. This also applies to any SQL version.
What does it mean? Essentially, not enabling the default is a step that is often overlooked in new installations, and frequently only manifests itself when a specific job is run (since as mentioned, clicking the test button will work). When setting up a recurring job, email alerts can be very helpful by providing notification when a job fails or succeed, as well as other useful information. To leverage this functionality, the following steps are required.
1. Set up the database email feature, which allows you to connect to an SMTP server. This is straightforward and there are many guides available to walk you through it. Here’s one: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail?view=sql-server-2017
2. After configuring the database email, set up your operators. Remember, you may have configured everything correctly, and even successfully sent a test, but still receive an error. Why? Often, instructions assume that you have already enabled the database email on the SQL server agent. To validate this, or resolve the email error, open SQL Server Agent > Properties:
3. In Properties, select Alert System. Under Mail session, check Enable mail profile:
4. After enabling the mail profile, close out of the Alert System and send a test email.
5. Now, the email should be received.
From this point forward, when you schedule jobs within the SQL agent with alerts and notification emails, you will not see, “ Note: Failed to notify ‘myoperatorname’ via email”. This is outside of the immediate scope of Dynamics and can be used for process that affect both online and on-premises. This also applies to any SQL version.
This solution helped us resolve our error – hopefully, it helps you, as well! To receive more helpful tips, subscribe to our blog.
As always, happy Dynamics 365’ing!