How to: Send Emails by Script Task in SSIS

Sending emails are common tasks in control flow of SSIS packages. We need to inform users when packages run completely or fail. Script Task in SSIS provides a more simple but more flexible approach than the 'Send Mail Task'. Using 'Script Task', we can customize our sending email tasks. That is to say, with 'Script Task', we can configure senders, recipients, subjects, and bodies of an e-mail message. OK, let's get started!

First, you need to add the following for the header of your script.

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net.Mail

Imports System.Net

Then, define variables:

Dim myHtmlMessage As MailMessage

Dim mySmtpClient As SmtpClient

The sender, recipient, subject, and body of an e-mail message may be specified as parameters when a MailMessage is used to initialize a MailMessage object.

Below is the whole script body:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain
  Public Sub Main()
    Dim myHtmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient

    myHtmlMessage =
    New MailMessage(sender, recipient, "Subject", "body1." + vbCrLf + vbCrLf + "body2:" + Dts.Variables("System::MachineName").Value.ToString + vbCrLf + "Execution by:" + Dts.Variables("System::UserName").Value.ToString + vbCrLf + "Package StartTime: " + Dts.Variables("System::StartTime").Value.ToString + vbCrLf + vbCrLf + strSuccessMessage)

   mySmtpClient =
    New SmtpClient(Dts.Variables("cvarSMTPServer").Value.ToString())
  mySmtpClient.Send(myHtmlMessage)

  Dts.TaskResult = Dts.Results.Success
  End Sub
End Class

Now you can send emails by executing the task!

More freebies to addon

If you would like to achieve even more, keep reading.

1. Attachments 
   
   In order to add attachment to emails, you need use    myHtmlMessage..Attachments.Add(New Attachment("c:\textfile1.txt"))
   

     

2. Users and Password
   
   The credentials returned by DefaultNetworkCredentials represents the authentication credentials for the current security context in which the application is running. mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
   
   If you don't want to use windows authentication to connect to your SMTP host and want to specify a different username and password, you can then use NetworkCredential class as shown below
   
    mySmtpClient.Credentials = New NetworkCredential(UserName, Password, Domain)