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

By | June 2, 2010

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:

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 Attachements.  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 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.


3 thoughts on “Send Email from SSIS with option to indicate Email User and Password

  1. sasi

    I tried this but its showing error as 0x8 at Script Task: The script returned a failure result.Help me to solve this

    Reply

Leave a Reply