Incorrect authentication data C# SSIS

41 views Asked by At

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
    };
}
0

There are 0 answers