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]
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
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
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.
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).
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."
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.
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."
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.
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.
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 "firstname.lastname@example.org."
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 "email@example.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).
After setting up the email object, Line 42 sends an email to "firstname.lastname@example.org" 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:("scriptmain",> _ 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)>
Update Send Email Table task
The final step in the foreach loop is to update the record's
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 10 shows how the "Parameter Mapping" is set up. Two package variables (
User::sendEmailFailed) are mapped to parameters of the sql task (
@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."
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 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.
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
sentEmailcolumn 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
sendEmailFailedcolumn=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.