SSIS Send Mail Task

In this section, we'll introduce a new task - Send Mail Task.

Send Mail Task is a task to send out e-mails. It is widely used in SSIS packages to send out an email to inform administrators if the package runs failed or an error is caught.

Just follow the steps below to see how to create the task to send out an email.

  1. Create a new package in solution LearnSSIS1 and rename it to SendMail.dtsx.

  2. Open the package if it is closed. Drag and drop the Send Mail Task to the package.

  3. Right click the task and choose "Edit..." to open the Send Mail Task Editor. In General tab, Change the name to "Send Mail Test" and the description to "Send an email".
  4. Send Mail Task Editor General Tab

  5. Click Mail tab, then click the drop down list in "SmtpConnection" and choose "" to open SMTP Connection Manager Editor.
  6. Send Mail Task Editor New SMTP Connection

    SMTP Connection Manager Editor

    You can see an SMTP Connection Manager was just created in the Connection Managers area. This is the first connection manager we introduced in this tutorial.

  7. Input your SMTP server and click "OK" button. Input an email addresses in From which means the email address sent from. Then click "OK".
  8. Error Message: No Recipient Is Specified

    Put you mouse on the red cross, a tool tip message "No recipient is specified" will shown up. That means the task has not been finished setting well the recipient email address is required. Checking the tool tip of the red cross icon is a quick method to identify what the current problem is.

  9. Right click the task and choose "Edit..." to open the editor again and set To, Cc or BCc fields. Apparently From and To are required and Cc and BCc are optional. Then click "OK".
  10. Warning Message: The subject is empty

    This time the icon is changed to yellow warning icon and the tool tip is shown as "The subject is empty".

  11. Reopen the editor again and input "Send Mail Test" in Subject and "This email is sent from a Send Mail Task for testing purpose." in MessageSource.

  12. Click "OK" button to save the settings and excute the package. You will receive the following email.
  13. An email example sent from a send mail task

An SMTP connection manager is used to connect to an SMTP (Simple Mail Transfer Protocol) server and a Send Mail Task can be set to use it to send out an email.

Any time right click the manager and choose "Edit..." to open SMTP Connection Manager Editor to change the following settings.

NameYesThe unique name of the SMTP connection manager
Description The description of the SMTP connection manager
SMTP ServerYesSMTP server name or IP address
Use Windows Authentication Windows authentication is used to authenticate access to the server if checked
Enable Secure Sockets Layer (SSL) e-mail is encrypted by SSL if checked
Timeout(milliseconds)  the number of milliseconds before timing out session connect

Let's continue working on the above package.

  1. Right click SMTP Connection Manager and choose "Edit..." to open SMTP Connection Manager Editor. Change its name to "SMTP Manager" and click "OK" to save the change.

  2. Right click the "Send Mail Test" task and choose "Edit..." to open Send Mail Task Editor. Click Mail tab, you will see the connection name was already changed in SmtpConnetion automatically.

  3. Click MessageSourceType drop-down list and choose Variable. Click "" to open "Add Variable" window.
  4. Message Source From a Variable

    There are 3 options to be chosen.

    • Direct Input: The MessageSource can be as email body directly as we did above.
    • File Connection: The MessageSource comes from a file so a file connection must be created. We'll demonstrate the file connection in late section.
    • Variable: The MessageSource comes from a variable you defined.

  5. Set the variable name to "Content" and the value to "This is from a variable.". Click "OK" to close the "Add Variable" dialogue. Then "User::Content" will be shown in the MessageSource.
  6. Add a Variable to hold the e-mail content

  7. Choose "High" in the Priority drop-down list. It includes High, low and Normal. Normal is the default setting.

  8. Click "..." button in the Attachments box and choose a file as its attachment. After the setting, it looks like below in the Mail tab. Then click OK.
  9. Send Mail Task Editor Mail Tab Settings

    You can add more files by repeating the above step 6. The paths of the files are saved together delimited by "|".

  10. Run the package and you will get an email with high priority and an attachment file. The content is exactly what we set in the variable.

Note Note
If the package runs on a server, the path of the attachment points to the server instead of your local machine.

As we know, an expression can be used to replace any property of a task. Assuming we got an error message which was saved in a variable "error". The following will demonstrate how to send out the error message.

  1. Open the above package if it is closed. Then create a new string variable "Error" with empty value.

  2. Drag and drop an Expression Task to the package and rename it to "Generate Error" then link it to "Send Mail Test" by a precedence constraint.

  3. Right click "Generate Error" and choose "Edit..." to open Expression Builder. Create the following expression and click "OK".

  4. @[User::Error] = "This is an error message."

    The package looks like below.

    An Expression Task Links to a Send Mail Task

  5. Right click "Send Mail Test" and choose "Edit..." to open Send Mail Task Editor. Click Mail tab, change MessageSourceType to "Direct Input" and remove the path of the attachment.

  6. Click Expressions tab, then click "..." button in the Expressions box to open Property Expressions Editor.

  7. Choose "MessageSource" in the drop-down list of Property column and click "..." button in the Expression to open Expression Builder.
  8. SSIS Property Expressions Editor

  9. Drag and drop the variable @[User::Error] to the Expression and click OK button to close the Expression Builder.

  10. Click "OK" twice to finish the settings.
  11. Send Mail Task with an Expression Added

  12. Run the package and you will receive an email with the content of "This is an error message."