Sending mail within SSIS - Part 2: Script Task

Case
I want to send mail within SSIS, preferably HTML formatted. What are the options?

Solutions

There are a couple of solutions to mail within SSIS:

1. Send Mail Task
2. Script Task with SmtpClient Class
3. Execute SQL Task with sp_send_dbmail
4. Custom Tasks like Send HTML Mail Task or COZYROC

To demonstrate the various solutions, I'm working with these four SSIS string variables. They contain the subject, body, from- and to address. Add these variables to your package and give them a suitable value. You could also use parameters instead if you're using 2012 project deployment.

Add these four variables to your package

B) Script Task
The Script Task is a little harder to use than the Send Mail Task (if you don't have .Net knowledge), but it doesn't have the same drawbacks as the Send Mail Task. It uses the SmtpClient Class and it has an HTML formatted mail option and a configurable port number.

1) SMTP Connection Manager
This first step is optional. I like to use as much as possible standard connection managers. Right click in the Connection Managers window and add an SMTP Connect manager. Add the SMTP server and change other options if necessary (other options are not used in this example). The alternative is to use an extra SSIS string variable for storing the SMTP Server.

SMTP Connection manager

2) Add Script Task
Add a Script Task to your Control Flow (or one of the event handlers). Give it a suitable name and add the SSIS variables as readonly variables to the Script Task.

Add all SSIS variables as ReadOnly

3) The Script
In the Scipt I'm using the variables and the connection manager to fill the properties of the SMTP client. Copy the contents of the Main method to your method and add the extra import/using on top.

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;          // Added

namespace ST_df6618207373422d961b80ca8b6a56e2
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Storing SSIS variables in .Net variables. You could skip this step and call the SSIS variables in the actual mail code
            // to reduce the number of code lines. Or you could fill these .Net variables with hardcoded values.
            String SendMailFrom = Dts.Variables["SendMailFrom"].Value.ToString();
            String SendMailTo = Dts.Variables["SendMailTo"].Value.ToString();
            String SendMailSubject = Dts.Variables["SendMailSubject"].Value.ToString();
            String SendMailBody = Dts.Variables["SendMailBody"].Value.ToString();

            // Get SMTP Server from SMTP Connection Manager. Alternative is to use extra variables or paramters instead:
            // String SmtpServer = Dts.Variables["SmtpServer"].Value.ToString();
            String SmtpServer = Dts.Connections["My SMTP Connection Manager"].Properties["SmtpServer"].GetValue(Dts.Connections["My SMTP Connection Manager"]).ToString();

            // Create an email and change the format to HTML
            MailMessage myHtmlFormattedMail = new MailMessage(SendMailFrom, SendMailTo, SendMailSubject, SendMailBody);
            myHtmlFormattedMail.IsBodyHtml = true;

            // Create a SMTP client to send the email
            SmtpClient mySmtpClient = new SmtpClient(SmtpServer);
            mySmtpClient.Port = 2525; // If you want to use a different portnumber instead of the default. Else remove this line.
            mySmtpClient.Send(myHtmlFormattedMail);

            // Close Script Task with success
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

or use VB.Net code

' VB.Net Code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail             ' Added

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()
        ' Storing SSIS variables in .Net variables. You could skip this step and call the SSIS variables in the actual mail code
        ' to reduce the number of code lines. Or you could fill these .Net variables with hardcoded values.
        Dim SendMailFrom As String = Dts.Variables("SendMailFrom").Value.ToString()
        Dim SendMailTo As String = Dts.Variables("SendMailTo").Value.ToString()
        Dim SendMailSubject As String = Dts.Variables("SendMailSubject").Value.ToString()
        Dim SendMailBody As String = Dts.Variables("SendMailBody").Value.ToString()

        ' Get SMTP Server from SMTP Connection Manager. Alternative is to use extra variables or paramters instead:
        ' Dim SmtpServer as String = Dts.Variables("SmtpServer").Value.ToString();
        Dim SmtpServer As String = Dts.Connections("My SMTP Connection Manager").Properties("SmtpServer").GetValue(Dts.Connections("My SMTP Connection Manager")).ToString()

        ' Create an email and change the format to HTML
        Dim myHtmlFormattedMail As New MailMessage(SendMailFrom, SendMailTo, SendMailSubject, SendMailBody)
        myHtmlFormattedMail.IsBodyHtml = True

        ' Create a SMTP client to send the email
        Dim mySmtpClient As New SmtpClient(SmtpServer)
        mySmtpClient.Port = 2525 ' If you want to use a different portnumber instead of the default. Else remove this line.
        mySmtpClient.Send(myHtmlFormattedMail)

        ' Close Script Task with success

        Dts.TaskResult = ScriptResults.Success
    End Sub

#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.

    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class

4) The result
Close the Script Task and execute it: An email message with html formatting!

HTML formatted email

If you're not into .Net, but really like TSQL then check out the next solution: Execute SQL Task with sp_send_dbmail