Click here to Skip to main content
15,867,330 members
Articles / Web Development / ASP.NET
Article

Invoke .Net Assembly using SSIS:Email Framework

Rate me:
Please Sign up or sign in to vote.
4.43/5 (3 votes)
21 Oct 2008CPOL3 min read 49.1K   215   27   6
This article demonstrate use of SSIS package that helps in invoking .Net Assembly having email component in it.

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

    State Diagram

    Logical Architecture

    State Diagram

    Interaction/Sequence Diagram

    State 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.

    C#
    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.

    C#
    public static void GenerateTraceLog(string content) 
            { 
                System.IO.File.AppendAllText(emailSettings["EmailTraceLogPath"], content); 
            } 

    This block send the mails through smtp server.

    C#
         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.

    C#
     public class NotificationManager
        {
            private static IDictionary<string, string> configSettings = new Dictionary<string, string>(); 
            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.

    State Diagram

    Set enable property of script task to true and double click the task block to open the dialog for scripting.

    State Diagram

    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.
  • State Diagram

    Now add reference to this package as shown in the screenshot.

    State Diagram

    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.

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


    Written By
    Technical Lead
    Australia Australia
    Whatsup-->Exploring--> MVC/HTML5/Javascript & Virtualization.......!
    www.santoshpoojari.blogspot.com

    Comments and Discussions

     
    GeneralNot sure about the approach Pin
    sandeep_122-Oct-08 11:52
    sandeep_122-Oct-08 11:52 
    GeneralRe: Not sure about the approach Pin
    santosh poojari22-Oct-08 18:23
    santosh poojari22-Oct-08 18:23 
    I do agree,we had earliar the window service which does this.There are list of activity we don't want to do due to reasons:
    1) To build Window service's custom scheduling code may take lot of our effort which involves testing whereas sql server scheduling is ready made tool available with flexible scheduling feature.
    2) We had window service installed in web server and it pulls huge amt of data from database server.As we have some policy constraint of insatlling Window service in db server .So we created ssis package in DB server .This approach helps us to improve performance as this was available in same block.
    Hope I convey the concern raised.

    Happy Coding
    "San"




    GeneralRe: Not sure about the approach Pin
    tec-goblin28-Oct-08 3:28
    tec-goblin28-Oct-08 3:28 
    GeneralRe: Not sure about the approach Pin
    santosh poojari29-Oct-08 22:12
    santosh poojari29-Oct-08 22:12 
    GeneralRe: Not sure about the approach Pin
    tec-goblin30-Oct-08 0:56
    tec-goblin30-Oct-08 0:56 
    GeneralRe: Not sure about the approach Pin
    Member 397829216-Apr-10 0:12
    Member 397829216-Apr-10 0:12 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.