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