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. 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 let's 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 is 2 steps, make it visible to your script which I will explain later and reference the script like this:

Dts.Variables["YourVariableName"].Value.ToString()

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;
 }
 else
 {
 //Fails the Task
 Dts.TaskResult = (int)ScriptResults.Failure;
 }
}

public bool SendMail(string sSubject, string sMessage, int iPriority)
{
 try
 {
 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] != "")
 {
 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;
 }

 //You can enable this for Attachments.  
 //SingleFile is a string variable for the file path.
 //foreach (string SingleFile in myFiles)
 //{
 //    Attachment myAttachment = new Attachment(SingleFile);
 //    message.Attachments.Add(myAttachment);
 //}

 message.Subject = sSubject;
 message.IsBodyHtml = true;
 message.Body = sMessage;

 smtpClient.Send(message);
 return true;
 }
 catch (Exception ex)
 {
 return false;
 }
}

Now on the top, you need to add the following references:

using System.Text.RegularExpressions;
using System.Net.Mail;

And that’s your code. That's simple. Now all you have to do is make 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.