What are you looking for ?
Home » » Send Mail in SSIS using GMail

Send Mail in SSIS using GMail


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.


Create 9 variables as show in below screenshot. Variable name can be anything. Its up to you.


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 code
enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

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;

            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["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] != "")
                if (sEmailCC != null)
                for (int i = 0; i < sEmailCC.Length; ++i)
                if (sEmailCC[i] != null && sEmailCC[i] != ��)
                switch (iPriority)
                    case 1:
                        message.Priority = MailPriority.High;
                    case 3:
                        message.Priority = MailPriority.Low;
                        message.Priority = MailPriority.Normal;
                Attachment myAttachment = new Attachment("C:/FF/test.txt");
                message.Subject = sSubject;
                message.IsBodyHtml = true;
                message.Body = sMessage;
                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.

That's all. Don't forget to check your inbox.
Share this article :

+ comments + 2 comments

12 October 2016 at 08:36

script is not working

8 February 2021 at 11:26

�� thanks ��

Post a Comment

Flag Counter

Social Profile

Copyright x 2011. By Wael Medhat - All Rights Reserved