question

OlivierLEGROS-7935 avatar image
0 Votes"
OlivierLEGROS-7935 asked OlivierLEGROS-7935 edited

Control of execution of SQL Server agents by mail: failure to send the mail in the event of an error

Hello everyone,

I use the management of mails on success and in case of failure in the execution of my packages via SQL Server agents
- Packages under Visual studio SSDT 2017 version 15.9.14
- deployment in Mangement studio v18.4 / SqlServer 2014
- Feeding an Azure database

The package is quite simple (in screenshot an example):

127136-capture.png

This package works perfectly if I run it from Visual Studio, I receive an email in the event of success and in the event of failure.
On the other hand, once deployed in my ETL, only success generates an email. If my task fails, I cannot find the trace of sending an email from the detailed report.
I have the impression that in the event of a crash in one of the tasks in my package, the agent quits the task (to go to the next step) without going to the end of all the tasks in the package (so the sending the mail, which Visual Studio does).
I tried to skip sending emails from the "event manager" tab (OnError / OnTaskFail): same result.
I have been looking for the solution on the web for several hours, but without result.

Do you have a solution to my problem?
(eventually I would like to delete the sending of the "success" mail and keep only the "failure" mail / for the moment I am obliged to do this by elimination, ie count the number of successful packages to deduce those in failure).

Thanking you in advance for your help.

Regards.


sql-server-integration-services
capture.png (91.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @OlivierLEGROS-7935,

Have you tried to send the mail from the SQL Server Agent job when the job fails?

You may refer this step by step.

In addition, you may consider the Event Handlers to trigger your mail task when any error occurs.

For example: ssis-event-handlers-basics

integration-services-ssis-event-handlers

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlivierLEGROS-7935 avatar image
0 Votes"
OlivierLEGROS-7935 answered OlivierLEGROS-7935 edited

Hello Zoe

thank you for your reply

  • The first solution: "send mail from SQL Agent work when job fails" from the agent's "notification" parameter only works if I have a single step in my agent. Otherwise I have several steps, I receive an email in case of failure BUT the agent stops and does not execute the other steps.
    I tested: in case of failure go to the next step AND in case of failure quit the job signaling the failure

  • The second solution: "using the event manager" does not work ... I do not receive the mail in case of error ("On Error" and "Task fail" event) since the execution of the agent (test on the control flows and on the sequence container)

Regards,

Olivier

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.