Using SSIS to send a HTML E-Mail Message with built-in table of Counts.
- by Kevin Shyr
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 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)
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
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:
