Introduction

In this world of information technology, sending emails is an important requirement for modern day applications. The applications are often required to send mails to various recipients for specific purposes like sending status, requesting an action, reporting errors, etc. The mail sending logic is usually based on the technology used for the development of the application.

I came across a requirement where I was required to send mails using a SQL Server Integration Services (SSIS) package. It's a simple task due to the availability of the 'Send Mail Task' in SSIS. But as per the requirement I had, the subject and the content of the mail was dependent upon the data returned from a database query and hence was required to be generated dynamically. I can perform this task using 'Send Mail Task' . But in such a case, where lots of flexibility is required in the mail functionality, the 'Script Task' of SSIS provides a more simple approach than the 'Send Mail Task'.

Purpose/Scope

This article is intended to explain a simple and time-efficient way of using the SSIS 'Script Task' to send configurable mails that provide more simplicity and better flexibility then the 'Send Mail Task'. The package will get the 'To' and 'From' email addresses from the package configuration file and will create the email subject and content using data returned from the database. The main focus of this article will be on sending mail using 'Script Task', which is explained using a simple example. I will assume here that the reader has the basic knowledge of SSIS and that he/she will be able to understand the terms related to SSIS. This article can also be used as a tutorial by creating the package as mentioned in the subsequent sections.

Background

I will take an example to demonstrate the use of script task for mail sending. My example's requirement goes like this: A mail is to be sent by the Order Processing department to the Invoice generation department requesting them to generate the invoice for a completed order. The mail should have the order number, order date and amount due for that order. The list of completed orders will be returned by a SQL query. The 'To' and 'From' email addresses have to be picked from the package configuration file. The sample email subject and content is given below:

Subject: Status for Order: <Order Number>
Message/Content: The order number: <Order Number> placed on <Order Date>is ready for shipment. Please generate invoice for amount: <Amount Due>

Get Started

For the sake of simplicity, the SQL query I am using in this sample package will return just one row. I will use this data while sending the mail. For query returning more than one rows, the result set can be imported into a recordset and than the script task (for mail) can be executed in a for loop for each record in that recordset. My query will run on AdventureWorks database and it goes like this:

SELECT TOP 1 Sales.SalesOrderHeader.OrderDate,
 Sales.SalesOrderHeader.TotalDue,
 Sales.SalesOrderHeader.SalesOrderNumber
FROM Sales.Customer
 INNER JOIN 
 Sales.SalesOrderHeader ON Sales.SalesOrderHeader.CustomerId = Sales.Customer.CustomerId
WHERE Sales.Customer.AccountNumber = 'AW00000003'

The query returns the following data:

Creating the Package

In this example we will create a new integration services project called 'SSIS_Email_Program' to demonstrate the email functionality using script task. Follow the following steps for creating the package:

1. Create a new SSIS project and name it 'SSIS_Email_Program'.

2. Now in the SSIS designer, drag the 'Execute SQL Task' and 'Script Task' on to the control flow tab of the package.

3. Declare the following package level variables with data type as shown below:

4. Go to menu item SSIS->Package Configurations and enable package configurations for this package. In the Package Configuration wizard window, select the 'Name' and 'Value' properties for user variables 'MailTo,MailFrom and SmtpServer'.

5. Create a new OLE DB connection manager which connects to AdventureWorks database on SQL Server instance used by you.

6. Set the properties for 'Execute SQL Task' as shown: Set 'Connection' (created in step 5) and 'SQLStatement' property on the General option

7. Set the mapping between query output columns and package variables as shown below (these variables will be used to create the subject and content of the email):

8. Connect the output of 'Execute SQL Task' to the input of 'Script Task' (named as 'Sending Mail using Script Task').

9. Double click the 'Sending Mail using Script Task' and set the 'ReadOnlyVariables' and 'ReadWriteVariables' properties on the 'Script' option.
ReadOnlyVariables- MailTo,MailFrom,SmtpServer,OrderNumber,AmountDue,OrderDate
ReadWriteVariables- MailSubject,MailMessage

Click the button 'Design Script' and add the Visual Basic code (provided resource file) to the script. Comments are included in the script to make it self-explanatory. The script simply performs the following tasks in the given sequence:

1. Get the Order Date, Order Number and Amount Due from package variable into local variable (the package variables were assigned the values returned by SQL query).
2. Creates a dynamic mail subject and message using variable values obtained from step 1.
3. Get 'To' and 'From' address for mail from the package variable which in turn get their value from config file.
4. Send mail using SMTP server mentioned in the config file (package variable 'SmtpServer' gets its value from config file which is used by the script).

The script performs the mail sending task in a very simple manner. Lot of other functionality like conditionally attaching files, sending mail to multiple recipients etc can be added to this script with just a little code change.

10. Before running the package, update the values for MailTo, MailFrom and SmtpServer in package configuration file.

Your package is ready to run now.

Conclusion

The mail functionality and flexibility that I have provided using Script Task can also be provided using expression builder in Send Mail task. Than why to use Script Task? The answer to this question is- 'when you have more than one option for doing a task, the simplest of all should be given the preference'. Using script task for sending mail provides simpler implementation for functionality like sending mails conditionally to multiple recipients, sending mails conditionally, generating dynamic mail subject/message/attachment etc. So next time when you are going to implement mail functionality in your SSIS package, give a thought to 'doing so using script task'.