Sending mail from SSIS

2016-11-19

SSIS toolbox provides Send Mail Task for sending emails from integration service. It allows only sending text mail with SMTP Connection Manager.

SMTP Connection Manager supports only anonymous and windows authentication. If you use mail server outside of the organization boundary or need to send html format mail this task is useless.

Here's the screenshots to describe this limitation.

Send Mail Task

SMTP Connection Manager

The alternative way, we could use Script Task to send mail directly or use Execute Process Task to trigger another process to send mails.

1. Send Mail using Script Task
 
    a) First we create a few variables to pass mail information to the task.

          Note that some ISPs block port 25, in this case you should use alternative port instead.

    b) Pass variables to the task.

    c) Scripts to be executed is as following. The namespace System.Net.Mail is needed in order to send mail.

        using System.Net.Mail;

        public void Main()

        {

            var smtpCredential = Dts.Variables["smtpCredential"].Value.ToString();

            var mailServer = Dts.Variables["mailServer"].Value.ToString();

            var mailPort = Dts.Variables["mailPort"].Value;

            var mailSender = Dts.Variables["mailSender"].Value.ToString();

            var mailRecepients = Dts.Variables["mailRecepients"].Value.ToString();

            var mailSubject = Dts.Variables["mailSubject"].Value.ToString();

            var mailBody = Dts.Variables["mailBody"].Value.ToString();

           

            var credential = smtpCredential.Split(',');

            var receipients = mailRecepients.Split(',');

            var user = credential[0];

            var pwd = credential[1];

            bool isHtml = true;

            MailMessage mail = new MailMessage();

            mail.IsBodyHtml = isHtml;

            mail.Subject = mailSubject;

            mail.Body = mailBody;

            mail.From = new MailAddress(mailSender);

            foreach (var m in receipients)

            {

                mail.To.Add(m);

            }

            SmtpClient smtp = new SmtpClient(mailServer, (int)mailPort);

            smtp.UseDefaultCredentials = false;

            smtp.Credentials = new System.Net.NetworkCredential(user, pwd);

            smtp.EnableSsl = false;

            smtp.Send(mail);

           

            Dts.TaskResult = (int)ScriptResults.Success;

        }

2. Send Mail using Execute Process Task
    
    By specifying the executable location, and optionally pass in some arguments, the task will trigger the other process to send mails. This especially useful in Service-Oriented Architecture.

Conclusion, there are many approaches for sending any kind of mails from integration service (SSIS) other than Send Mail Task.