{[['
']]}
']]}
Send Email from SSIS with option to indicate Email User and Password
If you had noticed, the built in Send Email task from the SSIS package does not have the option of indicating a user name and password, it will only authenticate using Windows Authentication, well that’s not good specially if you have an email provider that does not use that authentication model like Gmail.
To solve that issue, we need a bit of coding so you will have the functionality to add an Email Server you like with User Credentials in runtime, thankfully SSIS has the Script Task which we can use for a lot of reasons, adding more flexibility to the already powerful SSIS tool.
So this will be our solution developing our own Send Email Function with option for User Credentials.Now let's start.
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
That's all. Don't forget to check your inbox.
First
Create 9 variables as show in below screenshot. Variable name can be anything. Its up to you.EmailPassword
EmailPort
EmailServer
EmailUser
EmailSendTo
EmailSendFrom
EmailSendFromName
EmailSubjects
EmailBody
Second
After creating those variables, you then need to create your Script Task, open the script task editor by double clicking on the icon you may already have on the designer.
In the Namespaces region add this lines
using System.Text.RegularExpressions;
using System.Net.Mail;
Before the main method add this lines
#region VSTA generated codeenum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
In the main method add this lines
public void Main()
{
string sSubject = Dts.Variables["EmailSubjects"].Value.ToString();
string sBody = Dts.Variables["EmailBody"].Value.ToString();
int iPriority = 2;
string sBody = Dts.Variables["EmailBody"].Value.ToString();
int iPriority = 2;
if (SendMail(sSubject, sBody, iPriority))
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
//Fails the Task
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
public bool SendMail(string sSubject, string sMessage, int iPriority)
{
try
{
string sEmailServer = Dts.Variables["EmailServer"].Value.ToString();
string sEmailPort = Dts.Variables["EmailPort"].Value.ToString();
string sEmailUser = Dts.Variables["EmailUser"].Value.ToString();
string sEmailPassword = Dts.Variables["EmailPassword"].Value.ToString();
string sEmailSendTo = Dts.Variables["EmailSendTo"].Value.ToString();
//string sEmailSendCC = Dts.Variables["EmailSendCC"].Value.ToString();
string sEmailSendFrom = Dts.Variables["EmailSendFrom"].Value.ToString();
string sEmailSendFromName = Dts.Variables["EmailSendFromName"].Value.ToString();
SmtpClient smtpClient = new SmtpClient();
MailMessage message = new MailMessage();
MailAddress fromAddress = new MailAddress(sEmailSendFrom, sEmailSendFromName);
//You can have multiple emails separated by ;
string[] sEmailTo = Regex.Split(sEmailSendTo, ";");
//string[] sEmailCC = Regex.Split(sEmailSendCC, �;�);
int sEmailServerSMTP = int.Parse(sEmailPort);
smtpClient.Host = sEmailServer;
smtpClient.Port = sEmailServerSMTP;
smtpClient.EnableSsl = true;
System.Net.NetworkCredential myCredentials =
new System.Net.NetworkCredential(sEmailUser, sEmailPassword);
smtpClient.Credentials = myCredentials;
message.From = fromAddress;
if (sEmailTo != null)
{
for (int i = 0; i < sEmailTo.Length; ++i)
{
if (sEmailTo[i] != null && sEmailTo[i] != "")
{
message.To.Add(sEmailTo[i]);
}
}
}
/*
if (sEmailCC != null)
{
for (int i = 0; i < sEmailCC.Length; ++i)
{
if (sEmailCC[i] != null && sEmailCC[i] != ��)
{
message.To.Add(sEmailCC[i]);
}
}
}*/
switch (iPriority)
{
case 1:
message.Priority = MailPriority.High;
break;
case 3:
message.Priority = MailPriority.Low;
break;
default:
message.Priority = MailPriority.Normal;
break;
}
Attachment myAttachment = new Attachment("C:/FF/test.txt");
message.Attachments.Add(myAttachment);
message.Subject = sSubject;
message.IsBodyHtml = true;
message.Body = sMessage;
smtpClient.Send(message);
return true;
}
catch (Exception exc)
{
return false;
}
}
Now save the script and close it.
In the ReadOnlyVariable add the 9 variables you have created.
Now right click on the script task editor and execute it.
+ comments + 2 comments
script is not working
�� thanks ��
Post a Comment