Click here to Skip to main content
13,089,536 members (64,177 online)
Click here to Skip to main content
Add your own
alternative version


27 bookmarked
Posted 21 Oct 2008

Invoke .Net Assembly using SSIS:Email Framework

, 21 Oct 2008
Rate this:
Please Sign up or sign in to vote.
This article demonstrate use of SSIS package that helps in invoking .Net Assembly having email component in it.


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();
               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)) { 
                        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)) { 
                        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; 
                    //This internal attachment through system 
                    if ((string.IsNullOrEmpty(mailContent.AttachmentPath) == false)) { 
                        GenerateTraceLog("AttachmentPath :" + mailContent.AttachmentPath); 
                        Attachment mailAttachment = new Attachment(mailContent.AttachmentPath); 
                    Attachment mailAttachmentEntity = null; 
                    if ((mailContent.AttachmentList != null)) { 
                        foreach (string attachmentPath in mailContent.AttachmentList) { 
                            GenerateTraceLog("AttachmentPath :" + attachmentPath); 
                            mailAttachmentEntity = new Attachment(attachmentPath); 
                    //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.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******"); 
                    else { 
                    GenerateTraceLog("*****EMAIL SENDING REPORT ENDED******"); 
                catch (Exception ex) { 

    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<string, string> configSettings = new Dictionary<string, string>(); 
            static NotificationManager()
                XmlDocument xmlDocument = new XmlDocument();
                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();
                    mailContent.MailTo = "";
                    mailContent.MailFrom = "";
                    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;
    (mailContent, templatePlaceHolder, configSettings["ET_DueForCreditPayment"]);
            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.


  • 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.
    From :xyz@xyz.comMail To :zzz@zzzz.comCC :BCC :Subject :THIS TEST MAILERBody:<html>
        <style >
    p {
     font-family: Verdana;
    </style >
    </head >
    <body >
        <p >
            <small >*** 
            <small>Credit Card No. 104562321373263 has due date on 10/21/2008 5:08:22 PM .
    Please pay you bill before due date. </small>
            <small>Regards </small>
            <small>ZCZC Bank</small>


    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.


    Email Template Parser: By Alexandra Email Templates


    Any correction,criticism and advise are most welcome.


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


    About the Author

    santosh poojari
    Technical Lead
    India India
    Whatsup-->Exploring--> MVC/HTML5/Javascript & Virtualization.......!

    You may also be interested in...


    Comments and Discussions

    GeneralNot sure about the approach Pin
    sandeep_122-Oct-08 11:52
    membersandeep_122-Oct-08 11:52 
    GeneralRe: Not sure about the approach Pin
    santosh poojari22-Oct-08 18:23
    membersantosh poojari22-Oct-08 18:23 
    GeneralRe: Not sure about the approach Pin
    tec-goblin28-Oct-08 3:28
    membertec-goblin28-Oct-08 3:28 
    GeneralRe: Not sure about the approach Pin
    santosh poojari29-Oct-08 22:12
    membersantosh poojari29-Oct-08 22:12 
    GeneralRe: Not sure about the approach Pin
    tec-goblin30-Oct-08 0:56
    membertec-goblin30-Oct-08 0:56 
    I'm happy I helped in some way Smile | :) .
    GeneralRe: Not sure about the approach Pin
    Member 397829216-Apr-10 0:12
    memberMember 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.

    Permalink | Advertise | Privacy | Terms of Use | Mobile
    Web03 | 2.8.170813.1 | Last Updated 22 Oct 2008
    Article Copyright 2008 by santosh poojari
    Everything else Copyright © CodeProject, 1999-2017
    Layout: fixed | fluid