SQLSending email from SQL Server Integration Services (SSIS)

ProblemSending an email is a frequent requirement to notify a user on the occurrence of certain events, especially if an unexpected event happens (for example sending notification on failure that could be either logical or physical). SSIS provides a built-in "Send Mail Task" to send email in these circumstances. The Send Mail Task is quite simple and straight forward in its configuration and use, but it has some inherent limitations for example, first it supports only sending plain text email (doesn’t support HTML formatted mail) and second it doesn’t support passing username and password while connecting to SMTP server (it only supports Windows authentication i.e. none windows authentication is not allowed) nor does it support specifying a SMTP port number to send emails if your SMTP server does not use the default value.

In part 1 of this tip series, I will first start my discussion on using the built-in Send Mail Task and then in part 2 of this tip series, I will discuss using the "Script Task" to overcome the limitations imposed by Send Mail Task. I will show how you can send HTML formatted mails from SSIS using the Script Task or rather the .Net capabilities from your SSIS package.

SolutionWhen you drag a Send Mail Task from the toolbox to the control flow, you will notice there are three pages when you right click on the task and select Edit.  On each page you will find a few settings which you might need to configure for sending emails.

These pages are:

• General Page – Here you specify the name and a small description for your Send Mail Task. Though these are not mandatory, but it’s a good practice to give a meaningful name and description.
• Expression Page – You use the Expressions page to edit property expressions and to access the Property Expressions Editor and Property Expression Builder dialog boxes. Property expressions update the values of properties when the package / task are run. The expressions are evaluated and their results are used at runtime instead of the values to which you set the properties when you configured the task. The expressions can include variables and the functions and operators that the expression language provides. For example, you can generate the subject line for the Send Mail task by concatenating the value of a variable that contains the string "Weather forecast for " and the return results of the GETDATE() function to make the string "Weather forecast for 4/5/2009". You can refer to this KB article to learn more on how to use expression in Send Mail Taskhttp://support.microsoft.com/kb/906547.
• Mail Page – This is a place where you specify most of the configuration for your Send Mail Task as shown in below image:

 Let me summarize the Mail Page configurations and give you a brief description of the above settings which you would normally do on this page as shown in the below table:

Property	Description
SMTPConnection	Select an SMTP connection manager in the list, or click <New connection…> to create a new connection manager. As discussed below, you have an option to attempt anonymous or Windows authenticated connection as well as enable Secure Socket Layer (SSL) to encrypt the communication. An SMTP connection manager enables a package to connect to a Simple Mail Transfer Protocol (SMTP) server.
From	Specify the e-mail address of the sender, which may be used by recipient of the mail to reply back.
To	Provide the e-mail addresses of the recipients, multiple recipients emails are separated with semicolons.
Cc	Specify the e-mail addresses, multiple recipients emails are separated with semicolons, of individuals who also receive copies of the message.
Bcc	Specify the e-mail addresses, multiple recipients emails are separated with semicolons, of individuals who receive blind carbon copies (Bcc) copies of the message.
Subject	Provide a subject line for your e-mail message. You may configure it to dynamically change its value using expression as discussed below.
MessageSourceType	Select the source type of the message that could be either Direct Input which allows you to directly type your message in the box provided or File Connection which points to a file containing your message or Variable which allows your message content to come from a SSIS variable.
Priority	Set the priority of the message, it could be either Low, Normal or High.
Attachments	Provide the file names of attachments to the e-mail message, multiple attachments are delimited by the pipe (|) character.

When you create an SMTP connection manager, a dialog box similar as shown below will come up. Here you specify a meaningful name and a small description for this SMTP connection manager and then you specify the SMTP Server. The SMTP connection manager supports only anonymous authentication and Windows authentication. It does not support basic authentication. Check “Enable Secure Socket Layer (SSL)” option if you want to encrypt communication using Secure Sockets Layer (SSL) while sending e-mail messages.

One thing to note here is if you execute your package interactively from BIDS it uses the security context of the currently logged on user, whereas if you schedule it by executing a SQL Server Agent Job then it uses the account under which SQL Server Agent is running to connect to the SMTP host.

---

So far we have learned about all of the settings of the Send Mail Task, now let me execute the package and see the result….so here goes the mail.

NoteYou can configure and send emails using "Send Mail Task" programmatically as well, more details about how this can be done, can be found here on the MSDN site.

CautionThe maximum allowed length of an expression is 4,000 characters. While using an expression take this limitation into consideration or else you will end up having an error as shown below:

If you are using expressions for the MessageSource property and your expectation is such that your email message source can grow to more than 4,000 characters then in that case instead of using expression use “MessageSourceType = Variable” and assign the value directly using a variable or think about using the Script Task to send emails (this will be discussed in part 2 of this tip series).

Conclusion

• In the part 1 of this tip series I discussed how you can easily configure and use the built-in Send Mail Task of SSIS to send plain text emails, we also then learned about some of its limitations.
• In part 2 of this tip series, I will be covering sending emails using the Script Task which overcomes the limitations imposed by Send Mail Task.

Next Steps

• Read more about the Sent Mail Task http://msdn.microsoft.com/en-us/library/ms142165.aspx
• Stay tuned for Part 2 of this series