multiple emails sent by sp_db_sendmail when run as SSIS package

Question

I've a procedure which generates a tab delimited text file and also sends an email with a list of students as attachment using msdb.dbo.sp_send_dbmail.

When I execute the procedure thoruhg SQL server management studio, it sends only one email. But I created a SSIS package and scheduled the job to run nightly. This job sends 4 copies of the email to each recipient.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'A'
,@recipients =  @email_address
,@subject = 'Error Records'  
,@query = 'SELECT * FROM ##xxxx'
,@attach_query_result_as_file = 1
,@query_attachment_filename = 'results.txt'
,@query_result_header = 1
,@query_result_width=8000
,@body = 'These students were not imported'

I've set following parameters to 0 (within database mail configuration wizard), to see if it makes any difference. But it didn't resolve the problem. AccountRetryAttempts 0 AccountRetryDelay 0 DatabaseMailExeMinimumLifeTime 0

Any suggestions?

Answer 1

I assume you have this email wired up to an event, like OnError/OnTaskFailed, probably at the root level.

Every item you add to a Control Flow adds another layer of potential events. Imagine a Control Flow with a Sequence Container which Contains a ForEach Enumerator which contains a Data Flow Task. That's a fairly common design. Each of those objects has the ability to raise/handle events based on the objects it contains. The distance between the Control Flow's OnTaskFailed event handler and the Data Flow's OnTaskFailed event handler is 5 objects deep.

Data flow fails and raises the OnTaskFailed message. That message bubbles all the way up to the Control Flow resulting in email 1 being fired. The data flow then terminates. The ForEach loop receives signal that the Data Flow has completed and the return status was a failure so now the OnTaskFailed error fires for the Foreach loop. Repeat this pattern ad nauseum until every task/container has raised their own event.

Resolution depends, but usually folks get around this by either only putting the notification at the innermost objects (data flow in my example) or disabling the percolation of event handlers.

Answer 2

Check the solution here (it worked for me as I was getting 2 at a time) - Stored procedure using SP_SEND_DBMAIL sending duplicate emails to all recipients

Change the number of retries from X to 0. Now I only get 1 email. It'll be more obvious if your users are getting 4 emails, exactly 1 minute apart.