SSIS package when executed through job executes successfully but it does not send email

Question

I have an SSIS package which has a script task that sends emails. When the package is executed locally from Visual Studio, the email is successfully sent. However when I deploy the package through SSIS catalog, create and execute the job, the job runs successfully but I am not receiving any mail. Also when executing the package from under the Integration Services Catalog, it executes without any issues, still no email is sent. I have created a proxy account that uses credential, I guess there might be a missing link or setup error somewhere within these steps. Does anyone know how the Proxy account principals should be set? Or could at all be related to this? I have already tried these:

I have created a c# Console app, just for the email sending part, and when executing that on the same server, it sends the email without any issues.

---

Additional info to my problem:

• So initially I also had the Task Version issue (SSIS: script task (vs15) not work when deploy on sql server 2014), but after redeploying it with correct ISDeploymentWizard.exe, then this issue was solved.
• I’m also using Office365, but 2FA is turned off, and email was sent without any issues when executing the script task from Visual Studio (so when the package execution failed, it correctly went to Event Handlers, executed the script task, and mail was sent).
• I have proxy account configured to execute the SSIS package, and it works for other packages/steps.
• I have the following script behind the Script Task:

String SendMailFrom = Dts.Variables["$Project::Sender"].Value.ToString();
String SendMailFromPassword = Dts.Variables["$Project::SenderPassword"].GetSensitiveValue().ToString();
String SendMailTo = Dts.Variables["$Project::ToMail"].Value.ToString();
String SMTPServerName = Dts.Variables["$Project::SMTPServerName"].Value.ToString();
int SMTPServerPort = int.Parse(Dts.Variables["$Project::Port"].Value.ToString());
String PackageName = Dts.Variables["System::PackageName"].Value.ToString();
String ErrorDescription = Dts.Variables["System::ErrorDescription"].Value.ToString();
String ErrorCode = Dts.Variables["System::ErrorCode"].Value.ToString();
String SourceName = Dts.Variables["System::SourceName"].Value.ToString();

String Subject = PackageName + " Package Failed On " + DateTime.Now.ToString();
String Message = PackageName + " Package Failed On " + DateTime.Now.ToString();

try

    MailMessage email = new MailMessage();
    SmtpClient SmtpServer = new SmtpClient(SMTPServerName);
    // START
    email.From = new MailAddress(SendMailFrom);
    email.To.Add(SendMailTo);
    email.Subject = Subject;
    email.Body = Message;
    //END

    SmtpServer.Port = SMTPServerPort;
    SmtpServer.UseDefaultCredentials = false;
    SmtpServer.Credentials = new System.Net.NetworkCredential(SendMailFrom, SendMailFromPassword);
    SmtpServer.EnableSsl = true;

    SmtpServer.Send(email);
}
catch (Exception ex)
{
}

Dts.TaskResult = (int)ScriptResults.Success; 

Answer 1

I wrote an article about that.

If you are using Gmail, Outlook365 or any other SMTP server that requires 2FA you are not gonna make it till you turn off 2FA for that e-mail address.

At the bottom of the same article you can find a query that you can use to setup a new e-mail address.

EDIT:

Then use SSIS to send an e-mail using the SQL Server account you have successfully created:

https://www.sqlshack.com/ssis-execute-t-sql-statement-task-vs-execute-sql-task/

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Your_Profile_Name',
     @recipients = 'Use a valid e-mail address',
     @body = 'The database mail configuration was completed successfully.',
     @subject = 'Automated Success Message';
GO

EDIT 2:

Apparently there eare ways to add a variable inside a T-SQL Statement Task:

• https://www.sqlshack.com/ssis-execute-t-sql-statement-task-vs-execute-sql-task/
• https://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/
• https://social.msdn.microsoft.com/Forums/security/en-US/d63df15e-e032-4c77-b329-b118c5986e23/passing-variable-from-script-task-to-execute-sql-task?forum=sqlintegrationservices
• How to pass variable as a parameter in Execute SQL Task SSIS?
• https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/passing-variables-to-and-from-an-ssis-task/

Answer 2

Thanks to Francesco, finally I have chosen the parameterized "Execute SQL Task" approach with Database Mail configured, and with the script proposed:

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Your_Profile_Name',
     @recipients = 'Use a valid e-mail address',
     @body = 'The database mail configuration was completed successfully.',
     @subject = 'Automated Success Message';
GO