Send SSIS package failure notification to Outlook email using no credentials-CodePudding

I'm new to SSIS and would like to send an email notification when a package fails. I'm using script task with the following code:

#region Namespaces
using System;
using System.Net;
using System.Net.Mail;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

public void Main()
        {
            // TODO: Add your code here
            String SendMailFrom = Dts.Variables["EmailFrom"].Value.ToString();
            String SendMailTo = Dts.Variables["EmailTo"].Value.ToString();
            String SendMailSubject = Dts.Variables["EmailSubject"].Value.ToString();
            String SendMailBody = Dts.Variables["EmailBody"].Value.ToString();

            try
            {
                MailMessage email = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient("smtp.office365.com");
                // START
                email.From = new MailAddress(SendMailFrom);
                email.To.Add(SendMailTo);
                email.Subject = SendMailSubject;
                email.Body = SendMailBody;
                //END

                SmtpServer.Port = 587;
                SmtpServer.Credentials = new System.Net.NetworkCredential(SendMailFrom, "Password");
                SmtpServer.EnableSsl = true;

                SmtpServer.Send(email);
                MessageBox.Show("Email was Successfully Sent ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
            Dts.TaskResult = (int)ScriptResults.Success;
        }

My first issue is that I can not get this task to work with my own credentials, I get error "System.IOException: Unable to read data from the transport connection: net_io_connection closed."

But even beyond that, I know its unwise to hardcode my own credentials into this script task which I want run by a SQL Agent Job. Is there a way to send this email without any credentials? I don't care where the email is from, only where it is sent to.

CodePudding user response：

SSIS Send Email task has lots of limitations.

It was created long time ago to work with Microsoft Exchange. It even doesn't support emails in HTML format.

Instead of the following line:

SmtpServer.Credentials = new System.Net.NetworkCredential(SendMailFrom, "Password");

You can try the following:

SmtpServer.UseDefaultCredentials = true;
SmtpServer.Credentials = CredentialCache.DefaultNetworkCredentials;

CodePudding user response：

I found the solution to my initial problem here

I was able to add the following line of code to run the script using my own credentials:

System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12

However still need to figure out a solution to running this script using sql agent job. Would there be credential issues if another user were to run the job?