Geeks With Blogs
My Digital Life in Data Flow Data Warehouse, MDX, Cube, SSIS, BizTalk, SQL, XML, SSAS, SSRS, .NET

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:

Posted on Wednesday, February 2, 2011 3:47 PM | Back to top


Comments on this post: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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.
Left by Noon on Mar 31, 2011 4:30 PM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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.
Left by LifeLongTechie on Apr 04, 2011 4:06 PM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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?
Left by chuck on Feb 15, 2012 1:49 PM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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.
Left by LifeLongTechie on Mar 01, 2012 2:47 PM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting Gravatar...
Hi,
Please let me know how to apply conditional formatting with this approach.
Left by Mustafa on Sep 17, 2012 6:19 AM

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

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

Thanks,
Left by LifeLongTechie on Sep 17, 2012 10:07 AM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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.
Left by Lavanya on Mar 01, 2013 3:44 AM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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.
Left by Lifelongtechie on Mar 01, 2013 2:26 PM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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.
Left by Steven on Aug 19, 2013 3:26 PM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting Gravatar...
You can find more info here, under "OperationType Dynamic Options" : http://msdn.microsoft.com/en-us/library/ms188474(v=sql.90).aspx
Left by LifeLongTechie on Aug 19, 2013 3:55 PM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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
Left by dennis wang on Aug 24, 2013 12:42 AM

# re: Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
Requesting 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.
Left by LifeLongTechie on Aug 26, 2013 6:11 AM

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

I am new to ssis. I tried to follow your steps. I am getting email with the coloumn names but the rows are blank. Not sure if the InternalStr_CountResultAfterXSLT is working. i have declared User::InternalStr_CountResultFromSQL as string. Please help
Left by Reva on Oct 02, 2014 10:12 AM

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

First, make sure your stored procedure will return records. If there are records, click on the link (if you are using SSMS, output cell mode) and you should be able to see the output XML. Make sure you understand PATH and ROOT in the XML clause and they match your node name. If these are not wrong and you are still having problems, let me know.
Left by LifeLongTechie on Oct 03, 2014 3:37 PM

Your comment:
 (will show your gravatar)


Copyright © Kevin Shyr | Powered by: GeeksWithBlogs.net | Join free