0

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.

SQL_Noob
  • 107
  • 6
  • 1
    Why not just use a Send Mail Task? Why reinvent the wheel with a Script Task here? – Thom A Dec 15 '22 at 16:04
  • Also, if this is being run from Agent, why not use Agent's built in Notification feature to send an email to an operator when the job fails? – Thom A Dec 15 '22 at 16:08
  • I was also getting errors using send mail task: "Error in processing. The server response was: 5.7.3 STARTTLS is required to send mail", thats when I came across a post recommending using script task. Agent job will be running multiple packages, on failure of executing a package I would want the job to continue on. Within SSIS when package fails, an email notification should be sent out. How would you suggest doing this notification all within the job? – SQL_Noob Dec 15 '22 at 16:12

2 Answers2

1

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;
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
  • Unfortunately this did not work, but thank you for the response! I did find a solution to my initial problem. I will post with link to where I found solution. But beyond this initial problem I see the script running into an issue when being run by sql agent job with different credentials – SQL_Noob Dec 15 '22 at 16:22
  • I shared with you two lines from my production environment. It picks up SQL Server Agent account, and works well. – Yitzhak Khabinsky Dec 15 '22 at 16:31
  • Hi, I tried again your lines of code and received errors. You can see the error messages in this post https://stackoverflow.com/questions/74868337/how-to-send-ssis-failure-notification-when-executing-from-sql-agent-job, please advise on how to fix. – SQL_Noob Dec 20 '22 at 20:57
0

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?

SQL_Noob
  • 107
  • 6