Large-scale web sites typically send thousands of emails a day. Often, the code generating these emails is in separate applications resulting in non-standardized, difficult to maintain code. The solution in this article proposes to use the power of SQL Server and SSIS to send emails.

This solution is used by Community Health Network, Indianapolis, IN, http://www.ecommunity.com, which supports eight medical campuses, 70+ physician practices, an online retailer for durable medical equipment (http://www.homehealthmedical.com), and multiple outlier facilities. The site sends thousands of emails a day: patient reminders and confirmations, online retail shopping order confirmations, administrator notifications, etc. 

The SSIS package in this article has been in place for 5 years and, 3,000,000 emails later, it is still doing its magic. Instead of having mulitple lines of code in multiple locations, we now have a standardized email functionality.  The solution is comprised of three basic blocks :

• PART 1: A table where the email information is stored
• PART 2: An integration services package that sends the email
• PART 3: A SQL Server Agent job that runs the package

PART 1: The Table (tbl_SendEmail) and Its Insert Procedure (usp_ins_sendEmail)

The DML statement for tbl_SendEmail :

CREATE TABLE [dbo].[tbl_SendEmail](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [dateSubmitted] [datetime] NOT NULL CONSTRAINT [DF_tbl_SendEmail_dateSubmitted]  DEFAULT (getdate()),
 [sentEmail] [tinyint] NOT NULL CONSTRAINT [DF_tbl_SendEmail_sentEmail]  DEFAULT ((0)),
 [sendersEmail] [varchar](200) NULL,
 [address] [varchar](MAX) NULL,
 [message] [varchar](MAX) NULL,
 [subject] [varchar](200) NULL,
 [fileName] [varchar](200) NULL,
 [sendersName] [varchar](MAX) NULL,
 [html] [varchar](200) NULL,
 [appID] [int] NULL,
 [appName] [varchar](200) NULL,
 [groupID] [int] NULL,
 [bccToAddress] [varchar](5000) NULL,
 [sendEmailFailed] [int] NULL CONSTRAINT [DF_tbl_SendEmail_sendEmailFailed]  DEFAULT ((0)),
 CONSTRAINT [PK_tbl_SendEmail] PRIMARY KEY CLUSTERED
(
 [id] ASC
) 

All apps insert records into the table via the following stored procedure:

CREATE procedure [dbo].[usp_ins_sendEmail]
  @sendersEmail VARCHAR(200),
  @toAddress VARCHAR(200) = NULL,
  @message VARCHAR(8000),
  @subject VARCHAR(200),
  @fileName VARCHAR(200) = NULL,
  @sendersName VARCHAR(200) = NULL,
  @html VARCHAR(200) = NULL
  ,@sentEmail TINYINT = NULL
 
AS
BEGIN
  INSERT INTO [dbo].[tbl_SendEmail] (
   [sendersEmail],[address],[message],[subject],[fileName],[html], [sendersName]
  , sentEmail,bccToAddress) VALUES (
   @sendersEmail,
   @toAddress,
   @message,
   @subject,
    @fileName,
    @html,
    @sendersName,
    ISNULL(@sentEmail, 0),
    @bccToAddress
END

Figures 0 and 0a show a record (broken across two lines) that is inserted by the stored proc [dbo].[usp_ins_sendEmail]  

Figure 0

Figure 0a

PART 2: The Package (EmailFromSendEmailTable)

The screen shot (figure 1) shows the package within the designer of SSIS. It looks pretty simple, yes, but it is extremely powerful. Remember, "simplicity is the ultimate sophistication"  (according to Leonardo da Vinci ). I'll walk through each step in detail, but here is a summary of how it works:

• The first task is an SQL Task named "GetUnsentEmail(s)." The task selects records where sent email = 0 and puts those records into an object variable.
• The Foreach Loop Container loops through each record in that object variable and:
  • puts column values into variables
  • executes the script task "Send Email"  which uses those variables to send the email
  • executes the sql task "Update send email table" which runs a procedure to set the value of column sentEmail to true or false for that record

Figure 1

Before I go through the package objects in detail, let's review the variables of the package. Figure 2 shows the variables. Most of these variables will be mapped to a column from the database. Those that aren't mapped to a database column are recsToSendEmailTo and smtpServer (each of those will be explained in detail later). Be aware of the "Scope" property of a variable-it needs to be set to the entire package.  Note that  recsToSendEmailTo is a data type of "Object." It is an object variable because the recordset from GetUnsentEmails will be put into it variable for the Foreach Loop Container to loop through. 

Figure 2

GetUnsentEmail(s) SQL task

The GetUnsentEmail(s) SQL task executes a stored proc that gets all the records where sentEmail = false. The stored procedure it executes is here:

CREATE PROCEDURE [dbo].[usp_sel_SSISsendEmailTable]
 @sentStatus TINYINT = NULL
 ,@id INT = NULL
AS
BEGIN
 SET NOCOUNT ON;
 
IF @id IS NOT NULL  --it is looking for a direct id.
BEGIN
  SET @sentStatus = NULL
END
ELSE
 BEGIN
  IF @sentStatus IS NULL
   SET @sentStatus = 0
 END
 
 SELECT id,  [dateSubmitted],
 CAST ([sentEmail] AS VARCHAR(10)) AS sentEmail,
 ISNULL([sendersEmail], '') AS sendersEmail,
 ISNULL([address], '') AS [address],
 [message],
 [subject],
 ISNULL([fileName],'') AS [fileName],
 ISNULL([sendersName], '') AS sendersName,
 ISNULL([html], 0) AS html,
 ISNULL(appID,0) AS appID,
 ISNULL(appName, '') AS appName
 ,ISNULL(bccToAddress, '') AS bccToAddress
 ,sendEmailFailed
 FROM [dbo].[tbl_SendEmail]
 WHERE sentEmail = ISNULL(@sentStatus, sentEmail)
 AND id = ISNULL(@id, id)
END

Take a look at the two screenshots of the Execute SQL Task Editor for the GetUnsentEmail(s) task (figures 3 and 4). The configuration is potentially confusing because of the editor's use of the term "Result Set" to label multiple configuration items. This task is configured by selecting "General" on the left (refer to figure 3),  then on the right, set "ResultSet" to "Full result set" (and set the other necessary properties--SQLStatement,SQLSourceType and Connection). 

Figure 3

After the General configuration is complete, click "Result Set" on the left (figure 4) and map the result set to the object variable User::recsToSendEmailTo (see right pane of figure 4). The list of variables set up earlier is available  in a drop down list in the "Variable Name" column. Simply choose the variable User::recsToSendEmailTo from that list and set the Result Name to "0."

Figure 4

The Foreach Loop Container

The Foreach Loop Container (figure 5)  loops through the record set (which was put into the variable User::recsToSendEmailTo by the GetUnsentEmails task). The "Send Email" task uses the  variables to send the email. I'll detail those steps after I explain the Foreach Loop Container properties. 

Figure 5

The properties of the Foreach Loop Container are somewhat tricky to set up. In the "Collection" configuration of the Foreach Loop Editor (figure 6)  the enumerator is "Foreach ADO Enumerator" and the User::recsToSendEmailTo variable is the "ADO object source variable."

Figure 6

After the Collection is set up, the "Variable Mappings" is used to map the columns from the record set to the variables. In Figure 7 you can see that the Variable column contains the variable name in the format of User::VARIABLENAME. The "Index" column contains the index of the record's column.

To make this more clear let's use our example record (refer to figure 0). The value of column index of 0 (column name of "id") is 3489807. The setup in Figure 7 is telling the foreach container to put the value of 3489807 into the package variable of User::id;, and so on down the list. 

Figure 7

The Script Task

This is the "work horse" of the package. Simply (or is it sophisticatedly?) put, the task uses .NET's System.Net.Email class to send the email. We'll go into details about the script code later. For now, look at figure 8 and note the ReadOnlyVariables property. The task uses the values of those variables in its code to get the subject, body, recipient address, etc of the email it is going to send.

NOTE: You may not be able to read all the variables in figure 8- for those who are trying to "create along at home" here are the variables that are in that list: emailTesters,smtpServer, recsToSendEmailTo, id, dateSubmitted, sentEmail, sendersEmail,toAddress, message, subject, facility, fileName, sendersName, html, appID, appName,bccList.

Figure 8

Refer to the code of the script task editor below. You'll see that lines 24 and 25 create the objects needed to assemble and send the email (MailMessage and SMTPClient). To understand how the code uses the variables lets refer to our example record (see figures 0 and 0a--which are placed here also to save some scrolling).  In the Foreach Loop Container configuration (figure 7) the variable User::ToAddress is mapped to column index of 4 (this is a zero-based count, so counting from zero beginning at the first column "id", left to right, in figure 0 we wind up at the column named "address"). That column has a value of "jdmillay@ecommunity.com."

Line 28 of the script task code is setting the ToAddress of the MailMessage object to the value of the variable User::ToAddress (so, for this iteration of the loop container, the address the email will be sent to is "jdmillay@ecommunity.com"). Line 38 is setting the subject to "CMS Error" (column named "subject", column index of 6, which was mapped to User::subject) and line 39 is setting the body of the email to "NONERROR:EmptySectionMenuXML" (column named "message", column index of 5 which was mapped to User::message).  Again, those columns were mapped to the variables in the Foreach Loop Container configuration (figure 7). 

Figure 0

Figure 0a

After setting up the email object, Line 42 sends an email to "jdmillay@ecommunity.com" with a subject of "CMS Error" and a body of "NONERROR:EmptySectionMenuXML."

Also, notice in the code that the variable sendEmailFailed  is set (lines 22 and 44). That variable is important because sendEmailFailed is used by the last task in the Foreach loop container where the status of the record is updated(more on the specifics later).  It is also the only ReadWrite variable of the script task (refer to figure 8 to see where that is set). Line 22 sets the value of sendEmailFailed to false, then the code does its thing (tries to send the email) and line 44 sets sendEmailFailed to true if there is an error. If no error sendEmailFailed remains false. 

Note that the "Host" property of the SMTPClient object being set on line 41 uses the smtpServer variable. That variable is not mapped to a database column, its value is set in the variable sheet (figure 2). I'm pointing that out for those "creating along at home" because the value will need to be changed according to your environment. 

1:Imports System
2:Imports System.Data
3:Imports System.Math
4:Imports Microsoft.SqlServer.Dts.Runtime
5:Imports System.Net.Mail

6: _
7: _
8:Partial Public Class ScriptMain
9:Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
10:
11:Enum ScriptResults
12:Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
13:Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
14:End Enum
15:
16:
17:
18:Public Sub Main()
19:       'FOR DEBUGGING: MsgBox(Dts.Variables("id").Value.ToString)
20:
21:       
22:       Dts.Variables("sendEmailFailed").Value = 0
23:        Try
24:            Dim msg As New MailMessage()
25:            Dim smtp As New SmtpClient
26:            msg.From = New MailAddress(Dts.Variables("sendersEmail").Value.ToString)
27:
28:            msg.To.Add(Dts.Variables("toAddress").Value.ToString)
29:
30:            'FOR DEBUGGING: MsgBox(Dts.Variables("toAddress").Value.ToString)
31:
32:           If Len(Dts.Variables("bccList").Value.ToString) > 0 Then
33:               msg.Bcc.Add(Dts.Variables("bccList").Value.ToString)
34:                'MsgBox(Dts.Variables("bccList").Value.ToString)
35:           End If
36:
37:            'End If
38:           msg.Body = Dts.Variables("message").Value.ToString
39:           msg.Subject = Dts.Variables("subject").Value.ToString
40:            msg.IsBodyHtml = True
41:           smtp.Host = Dts.Variables("smtpServer").Value.ToString
42:            smtp.Send(msg)
43:       Catch ex As Exception
44:           Dts.Variables("sendEmailFailed").Value = 1
45:       Finally
46:
47:       End Try
48:        Dts.TaskResult = ScriptResults.Success
49:    End Sub
50:
51: End Class
52: (false)>("scriptmain",>

Update Send Email Table task

The final step in the foreach loop is to update the record's  emailSent and sendEmailFailed columns. This is done via the SQL task named "update send email table".  The stored proc that the task runs is:

Create PROCEDURE [SendEmail].[usp_upd_SSISsendEmailTable]
 @id INT = NULL,  @sendEmailFailed INT = 0 
AS 
BEGIN  
SET NOCOUNT ON; 
UPDATE e  SET sentEmail = 1, sendEmailFailed = @sendEmailFailed  FROM [SendEmail].[tbl_SendEmail] e  WHERE id = ISNULL(@id, ID) AND [sentEmail] = 0 
END

See figures 9 and 10 for the configuration of this task. In figure 9, the SQLStatement property is set to:

exec [usp_upd_sendEmailTable] @id = @id, @sendEmailFailed = @sendEmailFailed

which means when executed, the SQL task executes a stored proc named usp_upd_SSISsendEmailTable and passes the package variables to the stored proc's parameters. 

Figure 9

Figure 10 shows how the "Parameter Mapping" is set up.  Two package variables (User::ID and User::sendEmailFailed) are mapped to parameters of the sql task (@id and @sendEmailFailed).  The parameters of the sql task are passed to the parameters of the stored proc via the sql statement's "@id = @id, @sendEmailFailed = @sendEmailFailed."

Figure 10

After all that work by the foreach loop container, the package is finally finished -- with that record. The loop continues, going through each record in the USER::recsToSendEmailsTo object until it loops through the last record in the data set. 

PART 3: The SQL Agent Job (EmailFromSendEmail Table)

This is third and final part of the process. The job is set up via the server's SQL Server Agent node in Management Studio's Object Explorer. Figure 11 shows the setup of the job's one and only step. The step's package property (bottom of figure 11) is set to the name of the package (\EmailFromSendEmailTable).  The "\" in that property indicates that the sql server the job is running on needs to look at its root level directory for the package. Be aware that the package does need to be deployed before setting up the job. Information on how to deploy a SSIS package can be found throughout SqlServerCentral's forums and writings (this article on deployment is very good). 

Figure 11

Figure 12 shows the schedule setup. We decided that one minute was the longest duration tolerance we wanted between the record being inserted and the email being sent to the recipient. You can see that the job runs every minute, every day.

Figure 12

Additional Remarks

Some developers may think using "the database" for a functionality that is typically reserved for application code is simply techno muscle-flexing... "sure you can do it, but why?" It's not a lunkhead concept, though.

The benefits are enormous, here are a few of the most impactful:

• resending an email when the recipient "accidentally" overlooks it, misplaces it, or just doesn't read it is simple...  set the sentEmail column to false for that record in the table and it gets resent a minute later by the next job run
• an email didn't get sent (meaning the record's sendEmailFailed column=1 )? the reason can be easily determined by looking at the column values of that record 
• notice the groupID column on tbl_SendEmail. We use that column to maintain the list of recipients for an email from "feedback forms" on our site (an in-depth description is for another writing). If an employee doesn't need to receive the feedback emails anymore their email address is simply removed from a table. No more storing lists of email addresses in config files.

SSIS is very, very powerful right "out of the box." It is more powerful when it is combined with outside of the box thinking. When a great technology comes together with some ingenuity--a powerful solution like the one described here is born.