Invoke .Net Assembly using SSIS:Email Framework

• Download EmailFramework - 104 KB

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*****"); 
                
                // Creating the instance of SMTP Client 
                smtpClient = new SmtpClient(hostName, port); 
                smtpClient.EnableSsl = false; 
                                                               
                GenerateTraceLog("Mail From :" + mailContent.MailFrom); 
                if ((Convert.ToInt16(emailSettings["IsProduction"]) == 1)) { 
                    //TO List 
                    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); 
                    } 
                    //CC List 
                    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)); 
                        } 
                    } 
                    //BCC List 
                    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"]); 
                } 
                
                //Subject 
                GenerateTraceLog("Subject :" + mailContent.Subject); 
                mailMessage.Subject = mailContent.Subject; 
                
                //Attachment 
                //This internal attachment through system 
                
                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); 
                    } 
                } 
                                
                //This is uploaded using external interface. 
                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; 
                
                //Parse Email Template 
                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";
                //Body Content 
                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.