Send Mail Task

The Send Mail task is used to send e-mail messages from within a SQL Server Integration Services (SSIS) package.

Purpose and Usage

This task is invaluable for notifying stakeholders about the status of package execution, delivering reports, or sending alerts based on specific conditions met during the data integration process. It leverages the Simple Mail Transfer Protocol (SMTP) to send emails.

Configuring the Send Mail Task

You can configure the Send Mail task using the SSIS Designer. The configuration involves several key properties:

General Tab

Name: A unique name for the task.
Description: An optional description of the task's purpose.
SendUsing: Specifies how the mail is sent. Options include:
- SmtpClient: Uses the System.Net.Mail.SmtpClient class.
- DtsMail: Uses the legacy CDOSYS component (less common now).
SmtpConnection: The name of the SMTP connection manager to use (if SendUsing is SmtpClient).

Mail Tab

To: The recipient(s) of the email. You can specify multiple recipients separated by semicolons.
From: The sender's email address.
Cc: Carbon copy recipients.
Bcc: Blind carbon copy recipients.
ReplyTo: The email address to which replies should be sent.
Subject: The subject line of the email.
MessageSource: The content of the email body. This can be typed directly, loaded from a file, or generated by an expression.
MessageSourceType: Determines how the MessageSource is interpreted (e.g., Direct, File, Expression).

Advanced Tab

AttachmentName: The name of an attachment to include.
AttachmentConnection: The connection manager for the attachment.
Priority: The priority of the email (Normal, Low, High).
Importance: The importance level of the email (Normal, Low, High).

Expressions Tab

You can use expressions to dynamically set properties of the Send Mail task, such as recipients, subject, and message body, based on runtime conditions or data.

Example: Dynamically setting the subject line:

@[User::PackageName] + " execution completed successfully on " + (DT_WSTR, 30) GETDATE()

Prerequisites

To use the Send Mail task with SmtpClient:

An SMTP server must be available and accessible from the machine running the SSIS package.
You need the SMTP server's address and port.
Authentication credentials might be required depending on the SMTP server configuration.

Note: When using the SmtpClient provider, ensure that the SSIS runtime has network access to the specified SMTP server. Firewall rules might need to be adjusted.

Related Tasks

Execute SQL Task: Often used to retrieve data for email content or to log execution status.
Script Task: Provides more flexibility for complex email formatting or logic not achievable with the Send Mail task alone.