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 if coding so you will have the functionality to add an Email Server you like with User Credentials in runtime, thankfully SSIS have the Script Task which we can use for a lot of reasons, adding more flexibility to the already powerful SSIS tool. With script task you can extend in developing using VB.Net or C#.Net
So this will be our solution developing our own Send Email Function with option for User Credentials.
Now lets start.
You only need 2 main things 1 is the variables and another one is the script.
First - To declare variables in SSIS. Here are the minimum variables you might need for your custom send email function.
Second – After creating those variables you then need to create your script, open the script task editor by double clicking on the icon you may already have on the designer
and it will open a script task editor window now click edit script, this will fire up the Visual Studio Scripting Task. So if you are a .Net developer this will be familiar.
Now to access your variables inside the Script all you have to do 2 steps, make it visible to your script which I will explain later and referencing to the script like this:
Now all you have to do is develop your Sending Email functionality and here is how I have done mine:
public void Main()
string sSubject = "Test Subject";
string sBody = "Test Message";
int iPriority = 2;
if (SendMail(sSubject, sBody, iPriority))
Dts.TaskResult = (int)ScriptResults.Success;
//Fails the Task
Dts.TaskResult = (int)ScriptResults.Failure;
public bool SendMail(string sSubject, string sMessage, int iPriority)
string sEmailServer = Dts.Variables["sEmailServer"].Value.ToString();
string sEmailPort = Dts.Variables["sEmailPort"].Value.ToString();
string sEmailUser = Dts.Variables["sEmailUser"].Value.ToString();
string sEmailPassword = Dts.Variables["sEmailPassword"].Value.ToString();
string sEmailSendTo = Dts.Variables["sEmailSendTo"].Value.ToString();
string sEmailSendCC = Dts.Variables["sEmailSendCC"].Value.ToString();
string sEmailSendFrom = Dts.Variables["sEmailSendFrom"].Value.ToString();
string sEmailSendFromName = Dts.Variables["sEmailSendFromName"].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;
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] != "")
if (sEmailCC != null)
for (int i = 0; i < sEmailCC.Length; ++i)
if (sEmailCC[i] != null && sEmailCC[i] != "")
message.Priority = MailPriority.High;
message.Priority = MailPriority.Low;
message.Priority = MailPriority.Normal;
//You can enable this for Attachements. SingleFile is a string variable for the file path.
//foreach (string SingleFile in myFiles)
// Attachment myAttachment = new Attachment(SingleFile);
message.Subject = sSubject;
message.IsBodyHtml = true;
message.Body = sMessage;
catch (Exception ex)
Now on the top you need to add the following references
And that’s your code. That simple. Now all you have to do is making the variables visible to your script. Once you exit the Script Editor you go back to this window and tick the variables you want to use.