Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

For the record, this can be just as easily done with a .NET class with a DLL call.  The two major reasons for this ending up as a SSIS package are:

  • There are a lot of SQL resources in the organization for maintenance, but not as many .NET developers.
  • There is an existing automated process that links up SQL Jobs (more on that in the next post), and this is part of that process.

 

To start, this is what the SSIS looks like:

The first part of the control flow is just for the override scenario.

 

In the Execute SQL Task, it calls a stored procedure, which already formats the result into XML by using "FOR XML PATH('Row'), ROOT(N'FieldingCounts')".  The result XML string looks like this:

<FieldingCounts>
  <Row>
    <CellId>M COD</CellId>
    <Mailed>64</Mailed>
    <ReMailed>210</ReMailed>
    <TotalMail>274</TotalMail>
    <EMailed>233</EMailed>
    <TotalSent>297</TotalSent>
  </Row>
  <Row>
    <CellId>M National</CellId>
    <Mailed>11</Mailed>
    <ReMailed>59</ReMailed>
    <TotalMail>70</TotalMail>
    <EMailed>90</EMailed>
    <TotalSent>101</TotalSent>
  </Row>
  <Row>
    <CellId>U COD</CellId>
    <Mailed>91</Mailed>
    <ReMailed>238</ReMailed>
    <TotalMail>329</TotalMail>
    <EMailed>291</EMailed>
    <TotalSent>382</TotalSent>
  </Row>
  <Row>
    <CellId>U National</CellId>
    <Mailed>63</Mailed>
    <ReMailed>286</ReMailed>
    <TotalMail>349</TotalMail>
    <EMailed>374</EMailed>
    <TotalSent>437</TotalSent>
  </Row>
</FieldingCounts>

 This result is saved into an internal SSIS variable with the following settings on the General tab and the Result Set tab:

 

Now comes the trickier part.  We need to use the XML Task to format the XML string result into an HTML table, and I used Direct input XSLT

And here is the code of XSLT:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" indent="yes"/>
  <xsl:template match="/ROOT">
        <table border="1" cellpadding="6">
          <tr>
            <td></td>
            <td>Mailed</td>
            <td>Re-mailed</td>
            <td>Total Mail (Mailed, Re-mailed)</td>
            <td>E-mailed</td>
            <td>Total Sent (Mailed, E-mailed)</td>
          </tr>
          <xsl:for-each select="FieldingCounts/Row">
            <tr>
              <xsl:for-each select="./*">
                <td>
                  <xsl:value-of select="." />
                </td>
              </xsl:for-each>
            </tr>
          </xsl:for-each>
        </table>
  </xsl:template>
</xsl:stylesheet>

 

 Then a script task is used to send out an HTML email (as we are all painfully aware that SSIS Send Mail Task only sends plain text)

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;
using System.Net;
 
namespace ST_b829a2615e714bcfb55db0ce97be3901.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
 
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
 
        public void Main()
        {
            String EmailMsgBody = String.Format("<HTML><BODY><P>{0}</P><P>{1}</P></BODY></HTML>"
                                                , Dts.Variables["Config_SMTP_MessageSourceText"].Value.ToString()
                                                , Dts.Variables["InternalStr_CountResultAfterXSLT"].Value.ToString());
            MailMessage EmailCountMsg = new MailMessage(Dts.Variables["Config_SMTP_From"].Value.ToString().Replace(";", ",")
                                                        , Dts.Variables["Config_SMTP_Success_To"].Value.ToString().Replace(";", ",")
                                                        , Dts.Variables["Config_SMTP_SubjectLinePrefix"].Value.ToString() + " " + Dts.Variables["InternalStr_FieldingDate"].Value.ToString()
                                                        , EmailMsgBody);
            //EmailCountMsg.From.
            EmailCountMsg.CC.Add(Dts.Variables["Config_SMTP_Success_CC"].Value.ToString().Replace(";", ","));
            EmailCountMsg.IsBodyHtml = true;
 
            SmtpClient SMTPForCount = new SmtpClient(Dts.Variables["Config_SMTP_ServerAddress"].Value.ToString());
            SMTPForCount.Credentials = CredentialCache.DefaultNetworkCredentials;
 
            SMTPForCount.Send(EmailCountMsg);
 
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Note on this code: notice the email list has Replace(";", ",").  This is only here because the list is configurable in the SQL Job Step at Set Values, which does not react well with colons as email separator, but system.Net.Mail only handles comma as email separator, hence the extra replace in the string.

The result is a nicely formatted email message with count information:

Print | posted on Wednesday, February 2, 2011 3:47 PM

Feedback

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by Noon at 3/31/2011 4:30 PM
Gravatar I'm trying to follow your process and having some trouble when I get down to the Script Task. I am not all that familiar with VB. I have no idea where to copy and modify the code into the Script Task - Edit Script. :(
Could you please help me with this? The other two tasks, XML and Execute SQL seem to be running fine when I run through debug.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by LifeLongTechie at 4/4/2011 4:06 PM
Gravatar The code is not in VB; it's C#. You only get that in SSIS 2008. If you are using 2005, you are out of luck, and you'll have to translate that into VB.

You only need to copy the code inside the Main method, but also remember to copy the "using" section.

Inside my main method, there are references to package variables. Those are for my process, you will have to replace them for your purpose.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by chuck at 2/15/2012 1:49 PM
Gravatar This is the best example I seen thus far with only having the last step (sending email) to complete. C# syntax is not my strong point and I was hoping that you could direct me to the two variables you are referring above so I could complete this exercise?

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by LifeLongTechie at 3/1/2012 2:47 PM
Gravatar Chuck,

If you are referring to the variable inside the "Dts.Variables[]" syntax, they are SSIS package variables. Unfortunately, you'll have to make a list of all the variable names you will be making reference to before working on your script task.

If you are talking about C# variables like "EmailCountMsg", they are your C# local variables. They are like the variable you "DECLARE" in your stored procedure, like a temp storage for your operation.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by Mustafa at 9/17/2012 6:19 AM
Gravatar Hi,
Please let me know how to apply conditional formatting with this approach.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by LifeLongTechie at 9/17/2012 10:07 AM
Gravatar Mustafa,

Can you elaborate on your business scenario to help me understand what you are trying to do?

Thanks,

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by Lavanya at 3/1/2013 3:44 AM
Gravatar Its working fine with sql queries. When I'm using oracle queries, Execute sql task throws error with the variable data type and resultset when it is set as xml. Now i changed the variable datatype to object and resultset to fullresultset,the execute sql task works fine but xml task editor throws error.Please help me how to resolve this.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by Lifelongtechie at 3/1/2013 2:26 PM
Gravatar Lavanya,

I don't have an Oracle DEV environment right now to test this out, but the function in oracle "DBMS_XMLGEN" looks promising. I would go down this road if avoiding .NET code is one of the objective of the project.

Going down the road of what you have done, you can stick a Script Task between the "Execute SQL Task" and the "XML Task". In the script task, convert your result object variable into a dataset, then use the DataSet.GetXML() method to get the XML format ( http://msdn.microsoft.com/en-us/library/system.data.dataset.getxml.aspx)

Taking a step back, my design principle has always been to break things down into as little component as possible (well, as time and budget allows) so that the application is easier to test, debug, maintain, and quicker to respond to change. In this case, if those things are not priority, you can always put the result into a temp table, then use a while loop to build your string (I know, I know, I'm cheating, this is almost like building a cursor).

Remember, because you want the email to look nicer, you are spending time to get to that resulting HTML code. But as with everything else in programming, there is always more than one way to get there.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by Steven at 8/19/2013 3:26 PM
Gravatar Thank you so much for this tutorial. It is super helpful. Just curious though, I can't find much information on the "Second Operand" and its use in the XML Task Editor. Could you elaborate please?
Thanks in advance.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by LifeLongTechie at 8/19/2013 3:55 PM
Gravatar You can find more info here, under "OperationType Dynamic Options" : http://msdn.microsoft.com/en-us/library/ms188474(v=sql.90).aspx

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by dennis wang at 8/24/2013 12:42 AM
Gravatar it is very example. thanks! can I ask u to provide the template for Config_SMTP_MessageSourceText
Config_SMTP_SubjectLinePrefix
Config_SMTP_MessageSourceText
I don't know how to setup variables

thanks

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

Left by LifeLongTechie at 8/26/2013 6:11 AM
Gravatar Dennis,

If you are talking about those referred to in the script task, they are simply part of the message you see in the last screenshot. If you are referring to how to read these variable from the config file, I would suggest the easieast way is to put some value into those variables, then go through the configuration wizard to generate your config file, and you can then modify it to whatever value you want it to be. Remember to generate one config file per variable if you want those to be shared by other packages.

Your comment:





 
 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski