SQL Server 2005 Integration Services - Part 39 - Send Mail Task

December 26, 2006

Following presentation of the most popular Data Flow transformations in SQL Server 2005 Integration Services, we are revisiting the topic of Control Flow tasks, in order to make our coverage more complete. Among the most common tasks that have not yet been discussed in our series is the Send Mail task, which will be the topic of this article. In addition to describing its characteristics, we will also explore similar functionality built into Database Engine, focusing on its benefits and its ability to leverage them in SSIS packages.

Not surprisingly, the purpose of the Send Mail task is to send e-mail messages. Their content and target are arbitrary, making it easy to incorporate this feature into a wide variety of scenarios, such as notifying about the outcome of package execution, alerting about conditions that might affect its processing, or relaying intermediary values generated by preceding tasks to an external, remote system. The task relies on an existing e-mail account (used as the sender), hosted on an SMTP server. The server must either permit anonymous connections or be able to verify access via Windows authentication. In case of the latter, the credentials applied depend on the way in which Send Mail task is launched. Running it interactively utilizes the security context of the currently logged on user, while scheduling it takes advantage of an account defined by a SQL Server Agent proxy (for more information on this subject, refer to "SQL Server Agent Subsystems" topic in SQL Server 2005 Books Online).

To familiarize yourself with the characteristics of the Send Mail Task, initiate a new project of Integration Services type in the Business Intelligence Development Studio. After you locate the task icon in the Toolbox, drop it onto the Control Flow area of the Designer interface, and select the Edit... item from its context sensitive menu. This action will activate the Editor window divided into three sections. In the first one, labeled General, you can modify default values of its Name and Description properties. The second one, called Mail, provides the ability to configure commonly used, e-mail specific settings:

• SmptConnection - points to an SMTP Connection Manager, which identifies an SMTP server responsible for sending out e-mails generated by the task. As mentioned before, depending on the server configuration, you have an option to attempt unauthenticated or Windows authenticated (the most common option in Microsoft Exchange environment) connection as well as enable Secure Socket Layer (SSL) to encrypt the communication.
• From - designates an e-mail address of the sender, used in case a recipient decides to reply to the original e-mail. Note, however, that its content is arbitrary (it does not need to correspond to a valid e-mail account).
• To, Cc, and BCc - identify one or more e-mail addresses of recipients (including "carbon copy" and "blind carbon copy" options). When using multiple entries within each category, separate them with semicolons.
• Subject - intended for the subject of an e-mail message.
• MessageSourceType - determines the source of message content, based on your selection from three possible choices - Direct Input, File Connection, or Variable.
• MessageSource - depending on the value of MessageSourceType, this entry might contain directly typed-in text, the name of a File Connection Manager pointing to a file containing your message, or the name of a variable where the message content is stored.
• Priority - set to High, Normal, or Low (how these values are interpreted depends on the SMTP system processing a message).
• Attachments - allows for including message attachments.

The purpose of Expressions, available in the last of the three sections, is to accommodate situations where any of the message properties listed above need to be assigned dynamically during package execution (for more information regarding this process, including some examples, refer to the Microsoft Knowledge Base article 906547).

Not surprisingly, the simplicity of configuring Send Mail task is, to a large extent, a direct result of its limited functionality. Among its more relevant drawbacks are its restrictive SMTP server authentication options (geared primarily towards the Microsoft Exchange environment). In addition, there is no inherent failover capability in case the target server is not available (even though you can emulate it by employing error detection and handling, in combination with additional Send Mail tasks, such an approach tends to be cumbersome). The task also does not directly support sending messages in HTML format.

Fortunately, there are a couple of ways to remediate these shortcomings. The first one involves implementing missing features with Visual Basic .NET code enclosed within the Script Task (leveraging messaging functions incorporated into the .NET Framework). While a bit more demanding, this approach not only allows for sending HTML-formatted messages but also accommodates basic authentication (which can be secured with SSL encryption). For more information on this subject, refer to "Sending an HTML Mail Message with the Script Task " article in SQL Server 2005 Books Online.

The second alternative is based on the Database Mail component, built into SQL Server 2005 Database Engine, replacing SQL Mail (available in earlier versions of SQL Server) which was complex to configure, demanding in terms of requirements, inefficient (implemented with extended stored procedures), and had limited feature set (lacking, for example, clustering support).

You are no longer forced to install a MAPI client (Outlook) on the SQL Server computer or create an Exchange mailbox and its MAPI profile for the SQL Server service account. Instead, all necessary setup steps are performed either within SQL Server Management Studio (or by directly executing the appropriate stored procedures that hide behind its friendly GUI) and messages are sent using the SMTP protocol to a target server. (This is handled in an out-of-process fashion by DatabaseMail90.exe, located in the MSSQL\Binn directory and running independently of the database engine). It is also possible to control the maximum size of attachments and their types (based on file extensions). Enhanced logging and auditing capabilities further contribute to the superiority of Database Mail over its predecessor.

In order to take advantage of these benefits, you need to first enable Database Mail functionality. To accomplish this, select the Configure Database Mail option from the context sensitive menu of the Database Mail node (located in the Management folder) of the SQL Server Management Studio. (Alternatively, you can use the SQL Server Surface Area Configuration tool, available from the Configuration Tools submenu of SQL Server 2005 menu for this purpose or execute the sp_configure stored procedure with 'Database Mail XPs', 1 parameters). This will launch Database Mail Configuration Wizard, which provides a straightforward interface for setting up practically all relevant properties. Start by creating an e-mail profile with associated accounts (note that this is different from a MAPI profile required by SQL Mail that we mentioned earlier). Each profile serves simply as a collection of individual e-mail addresses, arranged in sequential order, including the parameters necessary to access their respective SMTP servers, such as server name or address, port number, and authentication method with their respective credentials (whenever applicable). Having multiple accounts within the same profile is beneficial for the purpose of redundancy, since the ones with lower priority are attempted in case those on top of the list fail. The mechanism for controlling access to profiles depends on whether they are marked as public or private. In case of the former, a profile is available to all members of the DatabaseMailUserRole msdb database role. With the latter, you need to explicitly assign permissions to individual msdb database users. Each profile is subject to restrictions imposed by System Parameters, which dictate delay and number of retries for each account (Account Retry Delay and Account Retry Attempts, respectively), maximum attachment size (Maximum File Size), prohibited attachment types (Prohibited Attachment File Extensions), minimum time that DatabaseMail90.Exe remains active once all messages are processed (Database Mail Executable Minimum Lifetime), and the type of events recorded in logs (Logging Level).

Once a profile and its accounts are configured, verify their correctness by executing the "Send Test E-Mail..." action from the context-sensitive menu of the Database Mail node of the SQL Server Management Studio. At this point, you are ready to send any custom messages using the msdb.dbo.sp_send_dbmail stored procedure, whose complete syntax and sample usage are described in sp_send_dbmail (Transact-SQL) article in SQL Server 2005 Books Online. When dealing with SQL Server Integration Services packages, you can take advantage of this stored procedure by implementing Execute SQL Task containing its invocation with an arbitrary set of parameters (rather than relying on more the limited Send Mail Task). This approach gives you additional benefits available in the Database Mail.

» See All Articles by Columnist Marcin Policht