SSIS - Email not sent from deployed package

Question

I am building a package that performs multiple tasks and sends a success email (from script task) as last step of the package.

When running the package manually from Visual Studio the email is sent correctly. However, when running the deployed package from a Job in SQL Server the email is never sent (I have checked in the Gmail account that sends the email and it does not appear in the "Sent" folder).

I also checked the report for the package execution and I can't find the send email task, so it seems that it never actually get to the script task that sends the email.

Any ideas what the problem might be?

Thanks!

score 3 · Answer 1 · answered Feb 10 '18 at 19:48

Ensure that you can send mail in SQL Server without Visual Studio involved. Also, the job from SQL Server sometimes runs as a different user than you when you login and run via Visual Studio. The problem may be that the script isn't running because the SQL Server user does not have permission to run a portion of the script. I would set this to run as an SSIS job in SQL Server and then check the SSIS log in SQL Server to see where the failure is occurring. If it is a different user issue, you can solve a multiple number of ways. You can give the SQL Server user the necessary permissions or you can setup a proxy account in SQL Server that has permission to run the job

Thanks! Seems like a permissions issue, but I am not sure why is this happening, since the user that executes the package is the NT SERVICE\SQLSERVERAGENT, which has "public" permissions and executes other jobs with emails without issues... — Luis Garcia-Moreno, Feb 12 '18 at 14:43

score 1 · Accepted Answer · answered Feb 16 '18 at 15:10

1

The following link answers this question. Before deploying to SSIS, change the SQL Server version:

SSIS: script task (vs15) not work when deploy on sql server 2014

answered Feb 16 '18 at 15:10

JECandela

56
2

score 0 · Answer 3 · answered Feb 12 '18 at 14:56

I have noticed something else while looking at the execution report, and now I am even more confused... (since other jobs that tigger emails are executed and everything is fine).

1) When executing the job from a job (executed by SQLSERVERAGENT) it seems like the task runs (since logs appear), but still the email is not sent:

2) When executing in SQL Server manually (with my user which is SysAdmin) an error appears:

SSIS - Email not sent from deployed package

3 Answers3

Linked