How to Use an SSIS Script Task to Send Email

November 4, 2022

The SSIS send mail task is straightforward and easy to configure. However, there are limitations to its usefulness. The Send Mail task can only send text-based emails, HTML formatting is not supported. Additionally, you cannot enter your credentials to connect to an SMTP server that is outside of your domain. Nor can you specify an SMTP port number to send emails if the value is other than the default. Fortunately, there is another option if the Send Mail task does not meet your needs. This tutorial will demonstrate how to use a script task using C# to successfully log into an email service, i.e., Gmail, and send an HTML formatted email with an attachment. This tutorial will provide you with the foundation for getting started. Once you understand the concept, you can change the variable names and code as needed.

Step 1

Create new SSIS project and add the following variables to your package.

Variable Name Scope Type Value

vEmailAttachment Package String Full path (including file name) to the file you want to attach.

vEmailFrom Package String Email address of the sender.

vEmailMessage Package String Message to be sent.

vEmailPassword Package String Password to the account.

vEmailSMTPServer Package String The SMTP server.

vEmailSubject Package String Subject of the email.

vEmailTo Package String Recipient of the email.

1.vEmailAttachment – Create the following directory C:\Export. Within the directory, create a text file, named EmailTestFile.txt. The path that should be entered as the variable value is C:\Export\EmailTestFile.txt. The variable value will provide the script task with the filename and location of the file to be attached.

2.vEmailFrom- Enter the email address of the sender as the variable value.

3.vEmailMessage – Enter the following text as your variable value. This is text is simply to demonstrate sending an HTML formatted email.

Hello,  This is an SSIS Test email, demonstrating HTML formatting using an SSIS script task.  Please see the attached file.  Thank You

4.vEmailPassword – Enter the password for the sender email account, as the variable value.

5.vEmailSMTPServer- Enter the SMTP server as the variable value. It is smtp.gmail.com for this example.

6.vEmailSubject – Enter the subject of the email as the variable value. It is Test - SSIS Script Task for Sending Email for this example.

7.vEmailTo- Enter the recipient email address for the variable value. I simply entered my test email account.

Step 2

Drag a script task onto the SSIS canvas and provide a name for the script task.

Step 3

Double click on the script task to configure it. Click on the elipses that appear to the right of ReadOnlyVariables to identify the variables as read only.

Mark the following variables as read only and click on the “OK” button.

Step 4

Add the necessary namespace.

using System.Net.Mail

Your script task should resemble the following.

Step 5

Assign the values from the project variables to your script variables inside the main method. We will also create a call to a method; we will build, to transmit the email, SendMailMessage.

public void Main()

{

string EmailSmtpServer = Dts.Variables["User::vEmailSMTPServer"].Value.ToString();

string EmailFrom = Dts.Variables["User::vEmailFrom"].Value.ToString();

string EmailTo = Dts.Variables["User::vEmailTo"].Value.ToString();

string EmailSubject= Dts.Variables["User::vEmailSubject"].Value.ToString();

string EmailMessage = Dts.Variables["User::vEmailMessage"].Value.ToString();

string EmailAttachment = Dts.Variables["User::vEmailAttachment"].Value.ToString();

string EmailPassword = Dts.Variables["User::vEmailPassword"].Value.ToString();

SendEmailMessage(EmailSmtpServer, EmailFrom, EmailTo, EmailSubject, EmailMessage, EmailAttachment, EmailPassword, true);

Dts.TaskResult = (int)ScriptResults.Success;

}

Step 6

Create the method, SendMailMessage. Once complete, save your changes and close out of C#. Click on “OK”.

public void SendEmailMessage(string Server, string Sender, string Recipient, string Subject, string Message, string FileAttachment, string Password, bool IsHtml)

{

MailMessage emailMessage;

SmtpClient emailSmtpClient;

try

{

System.Net.Mail.Attachment attachment;

attachment = new System.Net.Mail.Attachment(FileAttachment); //Add attachment

emailMessage = new MailMessage(Sender, Recipient, Subject, Message);

emailMessage.IsBodyHtml = IsHtml;

emailMessage.Attachments.Add(attachment);

emailSmtpClient = new SmtpClient(Server);

emailSmtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;

emailSmtpClient.UseDefaultCredentials = false;

emailSmtpClient.Credentials = new System.Net.NetworkCredential(Sender, Password);

emailSmtpClient.Port = 587;

emailSmtpClient.EnableSsl = true;

emailSmtpClient.Send(emailMessage);

}

catch (Exception e)

{

MessageBox.Show(e.ToString());

}

Step 7

Run the script task. If you are successful, you should see an email arrive at the email address you selected, resembling the following.

Code in its entirety

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.Net.Mail; //Necessary for email transmission.

#endregion

namespace ST_1fcb56743c2a4c0ebcc6a7f951e2112d

{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase