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:
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
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.