sp_send_dbmail not executing in execute sql task without throwing any error

Question

I am newbie to SSIS. I am facing an issue executing sp_send_dbmail in one of my stored procedures which is being run by Execute SQL task in SSIS. Main problem is, it is not throwing any error and completing successfully even though profile with which I am running this SP doesn't exist. I suspect there is a configuration issue but I am not able to diagnose it.

I have found that SMTP is not configured on my staging server. Can it be the only reason? Even if it is, it should at least throw an error, but in logs I do not see any error messages for the same.

Also, if I am running this SP directly through SQL, I get "Profile doesn't exists" error. But when I am running same SP through SSIS (execute sql task), then it is executing successfully.

Any guidance on this issue can be a great help.

This is how I am calling sp_send_dbmail.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Stagging Trigger',
@recipients = '[email protected]',
@subject = 'this is the SP to send mail from SP'

Answer 1

sp_send_dbmail only queues up the mail. Does not attempt to deliver, does not validate the email profile, cannot raise any of the errors you complain are missing.

Read here how to check the status of emails queued: Check the Status of E-Mail Messages Sent With Database Mail:

USE msdb ;  
GO  

-- Show the subject, the time that the mail item row was last  
-- modified, and the log information.  
-- Join sysmail_faileditems to sysmail_event_log   
-- on the mailitem_id column.  
-- In the WHERE clause list items where danw was in the recipients,  
-- copy_recipients, or blind_copy_recipients.  
-- These are the items that would have been sent  
-- to danw.  

SELECT items.subject,  
    items.last_mod_date  
    ,l.description FROM dbo.sysmail_faileditems as items  
INNER JOIN dbo.sysmail_event_log AS l  
    ON items.mailitem_id = l.mailitem_id  
WHERE items.recipients LIKE '%danw%'    
    OR items.copy_recipients LIKE '%danw%'   
    OR items.blind_copy_recipients LIKE '%danw%'  
GO  

More articles on the topic:

• Troubleshooting Database Mail Failures
• Complete Troubleshooting Guide for SQL Server Database Mail