Click here to Skip to main content
15,867,330 members
Articles / Programming Languages / C#

Send Email from SSIS with option to indicate Email User and Password

Rate me:
Please Sign up or sign in to vote.
4.90/5 (5 votes)
2 Jun 2010CPOL2 min read 102.4K   14   7
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:

C#
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:

C#
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:

C#
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.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

Comments and Discussions

 
GeneralSend email via exchange server Pin
Yuuji_S4-Jan-18 19:53
Yuuji_S4-Jan-18 19:53 
QuestionWow nice script! Pin
Antonio Grazioli15-Feb-17 23:39
Antonio Grazioli15-Feb-17 23:39 
QuestionAdd the variable sEmailSendCC Pin
Member 127816207-Oct-16 6:13
Member 127816207-Oct-16 6:13 
QuestionExecution Pin
petemill6626-Apr-16 5:22
petemill6626-Apr-16 5:22 
QuestionBig issue Pin
said maroc1-Apr-16 6:28
said maroc1-Apr-16 6:28 
AnswerRe: Big issue Pin
Member 1276054926-Sep-16 8:29
Member 1276054926-Sep-16 8:29 
QuestionTo send via Gmail Pin
Ampelmann14-Sep-11 21:33
Ampelmann14-Sep-11 21:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.