SSIS send mail task doesn't run through SQL Server agent job

Question

I have created SSIS package with Send mail task

It works (sends one email with multiple attachments) when I run SSIS package on SQL Server Data tools for Visual Studio

But when I run same package through SQL Server Agent -> Job it doesn't send email.

In SQL Server I have created below,

Under Security -> Credentials -> Added UserName

Under SQL Server Agent -> Proxies -> SSIS Package Executions -> Added Credential name

I have created SQL Server Agent -> Job

Still SQL Server Agent Job (SQL Server 2014) fails to send email.

Answer 1

I had the same issue (would work in VS but not when run as job in SQL). When the job runs, it uses whatever Windows credentials the SQL agent job runs under. It failed for me, even though I used the same Windows account used to successfully run the VS project as a proxy account to run the SQL job.

I solved it by passing SMTP account user & password credentials in the connection string. In my case I was using Googlemail SMTP - use whatever user & password parameters your SMTP server uses. Replace the bits in bold with your SMTP server, username & password

Steps in SSIS project:-

Create a SMTP Connection Manager with a parameterized ConnectionString property with a string which contains the smtp user and password.

1. Create connection using New Connection... option selecting SMTP as type.
2. Save without any connection settings. Give it any name you want.
3. Right click the connection and select Parameterize...
4. Select Property = ConnectionString
5. Select Create new parameter (e.g. SMTPConnectionManager_ConnectionString)
6. Set Value to connection string (e.g. SmtpServer=aspmx.l.google.com; port=25; UseWindowsAuthentication=False;EnableSsl=False; [email protected]; password=password123)
7. Set scope at appropriate level for your deployment method (Package or Project).
8. Click OK.

The beauty of SSIS is most properties can be parameterized/replaced with expressions to make it do what you want.