Introduction
There was a problem statement where we required to send auto mailer to users which
were approx. around 25000. In such case we need to pull the details from the database server to web server where window service is installed to send mailer. This approach has really hit our design to larger extent in terms of
performance. So we change the design to shift our whole architecture to database server. So that load on web server is
reduced. Now shifting this is to database server had a problem, as we have email
templates content in HTML format stored in Flat file, reading this flat file form
SQL server was the problem. One approach was to save these email content in database
table. But editing these email content involves lot of testing and review effort.
So we resorted to SSIS (Sql Server Integration services) which invokes .net assembly
that contains email component. SSIS package invokes notification method from this class which in turns fetches user details from database and pick the correct email template to send the mail to recipient.
This package is scheduled as job in SQL Server 2005 under SQL Server Agent. This
not only solve the email content management but also reduced load on web server.
This is so far proved to be a good working model for our business solution.
System Requirement
SQL Server 2005
SQL Server Integration service component Installed.
SMTP Port Enabled for Email sending.
Features Of Email Framework
Email content is stored in Flat file. There is a placeholder in email content which gets replaced at run time.
The component is scheduled in SQL Serve Agent as Job
Email Component is configurable for production or test environment. Email service start /stop mechanism is provided for test environment.
Email Content logging mechanism is implemented.
All the deployment is solely done in database server.
Physical Architecture
Logical Architecture
Interaction/Sequence Diagram
Implementation Of Email Framework
Lets start with actual implementation of Email Component.
Create Email Component
This static constructor loads the configuration settings from app.config file.As these assembly is in GAC so referencing its app.config is a problem. So we have given the physical path.
All the setting information is stored in generic collection Dictionary.So one has to pass key to fetch required values.
static EmailServices()
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load("D:/EmailFramework/EmailFramework/EmailFramework/app.config");
XmlNodeList nodeList = xmlDocument.SelectNodes("configuration/appSettings/add");
int index = 0;
foreach (XmlNode node in nodeList) {
string key = node.Attributes.GetNamedItem("key").Value;
string value = node.Attributes.GetNamedItem("value").Value;
emailSettings.Add(key, value);
index += 1;
}
}
This log mail content into text file.
public static void GenerateTraceLog(string content)
{
System.IO.File.AppendAllText(emailSettings["EmailTraceLogPath"], content);
}
This block send the mails through smtp server.
public static void SendRegisterNotification(MailContent mailContent, Hashtable templateVars, string templateFileName)
{
string hostName = null;
System.Net.Mail.SmtpClient smtpClient = null;
int port = 0;
MailMessage mailMessage = new MailMessage();
try {
hostName = emailSettings["SMTPServerName"];
if (hostName == null | hostName == string.Empty) {
throw new Exception
("Invalid Configuration.
The configuration could not be found. Please contact support professionals.");
}
port = Convert.ToInt16(emailSettings["PortNumber"]);
if (port == 0) {
port = C_DEFAULT_PORT;
}
GenerateTraceLog("***EMAIL SENDING REPORT STARTED*****");
smtpClient = new SmtpClient(hostName, port);
smtpClient.EnableSsl = false;
GenerateTraceLog("Mail From :" + mailContent.MailFrom);
if ((Convert.ToInt16(emailSettings["IsProduction"]) == 1)) {
if ((mailContent.ToList == null)) {
GenerateTraceLog("Mail To :" + mailContent.MailTo);
mailMessage = new MailMessage(mailContent.MailFrom, mailContent.MailTo);
}
else {
foreach (string recipient in mailContent.ToList) {
GenerateTraceLog("To List :" + recipient);
mailMessage.To.Add(new MailAddress(recipient));
}
mailMessage.From = new MailAddress(mailContent.MailFrom);
}
if ((mailContent.CCList == null)) {
GenerateTraceLog("CC :" + mailContent.MailCC);
if ((string.IsNullOrEmpty(mailContent.MailCC) == false)) {
mailMessage.CC.Add(mailContent.MailCC);
}
}
else {
foreach (string recipient in mailContent.CCList) {
GenerateTraceLog("CC List :" + recipient);
mailMessage.CC.Add(new MailAddress(recipient));
}
}
if ((mailContent.BCCList == null)) {
GenerateTraceLog("BCC :" + mailContent.MailBCC);
if ((string.IsNullOrEmpty(mailContent.MailBCC) == false)) {
mailMessage.Bcc.Add(mailContent.MailBCC);
}
}
else {
foreach (string recipient in mailContent.BCCList) {
GenerateTraceLog("BCC List :" + recipient);
mailMessage.Bcc.Add(new MailAddress(recipient));
}
}
}
else {
mailMessage = new MailMessage(mailContent.MailFrom, emailSettings["ToTestID"]);
}
GenerateTraceLog("Subject :" + mailContent.Subject);
mailMessage.Subject = mailContent.Subject;
if ((string.IsNullOrEmpty(mailContent.AttachmentPath) == false)) {
GenerateTraceLog("AttachmentPath :" + mailContent.AttachmentPath);
Attachment mailAttachment = new Attachment(mailContent.AttachmentPath);
mailMessage.Attachments.Add(mailAttachment);
}
Attachment mailAttachmentEntity = null;
if ((mailContent.AttachmentList != null)) {
foreach (string attachmentPath in mailContent.AttachmentList) {
GenerateTraceLog("AttachmentPath :" + attachmentPath);
mailAttachmentEntity = new Attachment(attachmentPath);
mailMessage.Attachments.Add(mailAttachmentEntity);
}
}
if ((string.IsNullOrEmpty(mailContent.AttachmentFileName) == false)) {
GenerateTraceLog("External Attachment :" + mailContent.AttachmentFileName);
Attachment mailAttachment =
new Attachment(mailContent.AttachmentFileContent, mailContent.AttachmentFileName);
mailMessage.Attachments.Add(mailAttachment);
}
mailMessage.IsBodyHtml = mailContent.IsBodyHTML;
mailMessage.Priority = (MailPriority)mailContent.MessagePriority;
if ((string.IsNullOrEmpty(mailContent.MessageText) == true)) {
EmailTemplateParser.EmailTemplateParser.Parser parser = null;
parser =
new EmailTemplateParser.EmailTemplateParser.Parser(templateFileName, templateVars);
mailMessage.Body = parser.Parse();
GenerateTraceLog("Body:" + parser.Parse());
}
else {
GenerateTraceLog("Body ");
GenerateTraceLog("Body Content :" + mailContent.MessageText);
mailMessage.Body = mailContent.MessageText;
}
if ((Convert.ToString(emailSettings["IsSendMail"]) == "NO"))
{
GenerateTraceLog("*****EMAIL SENDING REPORT ENDED******");
mailMessage.Dispose();
return;
}
else {
smtpClient.Send(mailMessage);
mailMessage.Dispose();
}
GenerateTraceLog("*****EMAIL SENDING REPORT ENDED******");
}
catch (Exception ex) {
GenerateTraceLog(
ex.Message + "***ERROR SENDING EMAIL THROUGH APPLICATION *****");
}
}
Create Notification Manager Class
This class contains all the methods and this each method is designated to script task package of SSIS which then scheduled as jobs.
public class NotificationManager
{
private static IDictionary configSettings = new Dictionary();
static NotificationManager()
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load("D:/EmailFramework/EmailFramework/EmailFramework/app.config");
XmlNodeList nodeList = xmlDocument.SelectNodes("configuration/appSettings/add");
int index = 0;
foreach (XmlNode node in nodeList)
{
string key = node.Attributes.GetNamedItem("key").Value;
string value = node.Attributes.GetNamedItem("value").Value;
configSettings.Add(key, value);
index += 1;
}
}
public static void NotifyDueForCreditPayment()
{
MailContent mailContent=new MailContent();
try
{
mailContent.MailTo = "xyz@addd.com";
mailContent.MailFrom = "xyz@addd.com";
mailContent.Subject = "THIS TEST MAILER";
Hashtable templatePlaceHolder = new Hashtable();
templatePlaceHolder.Add("To", "xyz");
templatePlaceHolder.Add("Credit_Card_Number",104562321373263 );
templatePlaceHolder.Add("Due_Date", DateTime.Now);
mailContent.IsBodyHTML = true;
mailContent.MessagePriority = 1;
mailContent.MessageText = string.Empty;
EmailServices.SendRegisterNotification
(mailContent, templatePlaceHolder, configSettings["ET_DueForCreditPayment"]);
}
catch
{
}
}
public static void NotifyCreditCardExpiry()
{
}
public static void NotifyApplicationForAddOnCard()
{
}
Create SSIS -Dtsx package :Script Task
Open business intelligence studio and create new ssis package. Drag script task into control pane screen.
Set enable property of script task to true and double click the task block to open the dialog for scripting.
Click on design script to open up vbscript coding section.
Note:
Add the EmailFramework and EmailTemplateParser dll into given folder.
Add the EmailFramework and EmailTemplateParser dll into GAC using gac
utility. Make sure you create strong name key for it. If strong name is created using visual studio ,make sure you compile and bulit the dll before you register into GAC cache.
Now add reference to this package as shown in the screenshot.
Deployment Into Production
Place the app.config file as mentioned in the code
Place the Email Template folder in the location as mentioned in app.config file.
Double click Install_Assembly_In_GAC.bat. This will install the assembly into GAC.
To test the package,execute package.Set SendMail="No" and check EmailLog.txt.
***EMAIL SENDING REPORT STARTED*****Mail
From :xyz@xyz.comMail To :zzz@zzzz.comCC :BCC :Subject :THIS TEST MAILERBody:<html>
<head>
<style >
p {
font-family: Verdana;
}
</style >
</head >
<body >
<p >
<small >***
THIS IS AN AUTOGENERATED MAIL.PLEASE DO NOT REPLY TO THIS MESSAGE ***</small></p>
<p>
<small>xyz</small></p>
<p>
<small>Credit Card No. 104562321373263 has due date on 10/21/2008 5:08:22 PM .
Please pay you bill before due date. </small>
</p>
<p>
<small></small>
<br>
<small>Regards </small>
<br>
<small>ZCZC Bank</small>
<br>
Demerit
Only problem with my design is that, app.config is explicitly parsed as it needs to be kept in GAC. Need help from someone who know how to configure assembly in GAC with config.
Reference
Email Template Parser: By Alexandra Email Templates
Conclusion
Any correction,criticism and advise are most welcome.
