In today’s world of information technology, sending emails is a crucial requirement for modern-day applications. Applications often need to send emails to various recipients for specific purposes such as sending status updates, requesting actions, reporting errors, and more. When it comes to sending emails using SQL Server Integration Services (SSIS), the ‘Send Mail Task’ is a commonly used component. However, there are cases where the subject and content of the email need to be dynamically generated based on data returned from a database query. In such scenarios, the ‘Script Task’ in SSIS provides a simpler and more flexible approach than the ‘Send Mail Task’.

Purpose/Scope

This article aims to explain a simple and time-efficient way of using the SSIS ‘Script Task’ to send configurable mails, offering more simplicity and better flexibility than the ‘Send Mail Task’. The package will retrieve the ‘To’ and ‘From’ email addresses from the package configuration file and generate the email subject and content using data returned from the database. This article assumes that the reader has basic knowledge of SSIS and can understand the terms related to it. It can also be used as a tutorial by following the steps mentioned in the subsequent sections.

Background

Let’s consider an example to demonstrate the use of the script task for sending emails. In this example, the Order Processing department needs to send an email to the Invoice Generation department, requesting them to generate an invoice for a completed order. The email should include 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 will be retrieved from the package configuration file. The sample email subject and content are as follows:

Subject: Status for Order: <Order Number>

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

For the sake of simplicity, the SQL query used in this sample package will return just one row. However, for queries returning multiple rows, the result set can be imported into a recordset, and the script task for mail can be executed in a loop for each record in that recordset. The query used in this example is:

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 SSIS project called ‘SSIS_Email_Program’ to demonstrate the email functionality using the script task. Follow these steps to create the package:

1. Create a new SSIS project and name it ‘SSIS_Email_Program’.
2. In the SSIS designer, drag the ‘Execute SQL Task’ and ‘Script Task’ onto the control flow tab of the package.
3. Declare the following package-level variables with the specified data types:

4. Go to the 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 the user variables ‘MailTo’, ‘MailFrom’, and ‘SmtpServer’.
5. Create a new OLE DB connection manager that connects to the AdventureWorks database on the SQL Server instance you are using.
6. Set the properties for the ‘Execute SQL Task’ as follows:

• Set the ‘Connection’ property to the OLE DB connection manager created in the previous step.
• Set the ‘SQLStatement’ property to the SQL query mentioned earlier.

7. Set the mapping between the query output columns and package variables as shown below:

8. Connect the output of the ‘Execute SQL Task’ to the input of the ‘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 ‘Design Script’ button and add the Visual Basic code provided in the resource file to the script. The script performs the following tasks:

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

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

Your package is now ready to run.

Conclusion

The script task provides a simpler implementation for functionality like sending emails conditionally to multiple recipients, sending emails conditionally, generating dynamic mail subject/message/attachment, and more. While the same functionality can be achieved using the expression builder in the ‘Send Mail Task’, the script task offers more simplicity and flexibility. When you have multiple options for performing a task, it is best to choose the simplest one. So, the next time you need to implement mail functionality in your SSIS package, consider using the script task.