0

I'm trying to send a list of missing products from a table through the mail. For that, I'm using a Script task in SSIS. I don't have any experience in C# as I'm an SQL developer. I don't know what's happening with this code. Sometimes it is successful and sometimes it is showing "Syntax error, Command unrecognized. The server response was: Incorrect authentication data" in an exception block and failing.

using System;
using System.Net.Mail;
using System.Net;
using System.Windows.Forms;
namespace ST_68a684eb301c4834b1baaa0684e152be
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            string MailTo = Dts.Variables["User::varSendMailTo"].Value.ToString();
            string MailFrom = Dts.Variables["User::varSendMailFrom"].Value.ToString();
            string MailCC = Dts.Variables["User::varSendMailCC"].Value.ToString();
            string EmailMessage = Dts.Variables["User::varEmailMessage"].Value.ToString();
            string MailBody = string.Empty;
            //string MailSub = string.Empty;

            MailBody = @" " + EmailMessage;

            EmailFunction(MailTo, MailBody, MailCC);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        public void EmailFunction(string MailTo, string MailBody, string MailCC)
        {
            string username = Dts.Variables["$Package::strMailUserName"].Value.ToString();
            string password = Dts.Variables["$Package::strMailPassWord"].Value.ToString();

            string MailSub = " Production Missing House Hold Tags List for " + DateTime.Now.ToString("dd-MMM-yyyy");
            string EmailTo = MailTo;

            string body = MailBody;

            SendEmail(username, password, EmailTo, MailSub, body, MailCC);
        }
        public void SendEmail(string username, string password, string EmailTo, string MailSub, string MailBod, string EmCC)
        {
            try
            {
                MailMessage mail = new MailMessage();
                mail.From = new MailAddress(username);
                mail.Subject = MailSub;
                mail.Body = MailBod;
                mail.IsBodyHtml = true;
                if (EmailTo != "")
                {
                    if (EmailTo.Contains(";"))
                    {
                        string[] toList = EmailTo.Split(';');
                        foreach (string EmailAddress in toList)
                        {
                            if (EmailAddress != "")
                            {
                                mail.To.Add(EmailAddress);
                            }
                        }
                    }
                    else
                    {
                        mail.To.Add(EmailTo);
                    }
                }
                if (EmCC != "")
                {
                    if (EmCC.Contains(";"))
                    {
                        string[] ccList = EmCC.Split(';');
                        foreach (string EmailAddress in ccList)
                        {
                            if (EmailAddress != "")
                            {
                                mail.CC.Add(EmailAddress);
                            }
                        }
                    }
                    else
                    {
                        mail.CC.Add(EmCC);
                    }
                }

                var client = new SmtpClient(Dts.Variables["$Package::strMailHost"].Value.ToString(), 587)
                {
                    UseDefaultCredentials = false,
                    Credentials = new NetworkCredential(username, password),
                    EnableSsl = true
                };
                client.Send(mail);
            }
            catch (Exception ex)

            {
                MessageBox.Show(ex.Message.ToString());
            }

        }

    }
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
}
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • 1
    SSIS has a [Send Mail task](https://learn.microsoft.com/en-us/sql/integration-services/control-flow/send-mail-task?view=sql-server-ver16). Why aren't you using it? As for the error itself, perhaps it's correct and the credentials are wrong? – Panagiotis Kanavos May 24 '23 at 10:05
  • Some suggestions: 1. google the error text (looks like it's SMTP authenticaton) 2. Isolate under what circumstances it fails and doesn't. 3. Find a guide on debugging SSIS script tasks and dumping variables – Nick.Mc May 24 '23 at 10:06
  • I agree with @PanagiotisKanavos comment there are many ways to email from SQL Server. Using an SSIS script is probably one of the most error prone methods. – Nick.Mc May 24 '23 at 10:06
  • @PanagiotisKanavos But if the credentials are wrong, why is it running successfully sometimes? I'm not changing anything when running. – Rajendra Gowd May 24 '23 at 10:08
  • Because you're using different credentials each time? We can't guess how you call that code, or what SMTP server you use. If you use GMail for free you may be hitting the daily limits for such an authentication method. GMail isn't a free email service nor does it like to be used that way. .NET's SmptClient is very old, doesn't support newer features and Microsoft itself warns against using it. – Panagiotis Kanavos May 24 '23 at 10:12
  • Once again, why are you using this code instead of the SSIS Send Mail task? If `Send Mail` fails, trying to send the same emails using the same credentials to the same server isn't going to work either – Panagiotis Kanavos May 24 '23 at 10:12
  • @PanagiotisKanavos I was trying to send the mail from the office live email address to some of the employees emails. I'm using the same credentials every time. The reason for not using the Send Mail task is because I don't have any right to modify the ETLs. They were created like that long way back. I can just debug the code and request permissions to change the code. – Rajendra Gowd May 24 '23 at 10:17
  • You say you cannot change this code, we can infer then that this has been working and has only recently started throwing the exception. That would imply that something "else" has changed. Has there been changes at the O365 administrative level? User email address or password get updated? Were the package parameters passed to the task (they should be required). As Nick identified, you should dump variable values to a log. I favor an approach like these 4 lines https://billfellows.blogspot.com/2016/04/biml-script-task-test-for-echo.html – billinkc May 24 '23 at 14:12
  • @billinkc Let's say I create a completely new ETL with the same steps and now without the script task, I will add send mail task in SSIS. There are only a few options in Send mail task (MailTo, MailFrom, MailCC, MailHost). I don't know how it will work. Can you please explain to me how can I send an email from a mail address without passing its password? And, Is it also possible to send a table of data from send mail task? – Rajendra Gowd May 25 '23 at 15:50
  • Nick/Panagiotis are the advocates of using a different email approach, which I fully support as well. I was simply observing that a formerly working task no longer works and I wanted to help you identify *what* event happened to make it so. Code doesn't rot over time so *something* affected it. To send a table of data, you would need to first write that data out into some format and then attach it to an email. In an SSIS world, I find that leveraging `msdb.dbo.sp_send_dbmail` is the correct answer in 98% of the cases. – billinkc May 25 '23 at 19:12

0 Answers0