no root - Page 268 - Developer IT

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:

Search Results

Search found 12017 results on 481 pages for 'no root'.

Page 268/481 | < Previous Page | 264 265 266 267 268 269 270 271 272 273 274 275 | Next Page >

- by Kevin Shyr

- by Bill Osuch

- by Yam Marcovic

- by PRajkumar

- by Anthony Shorten

- by Tanu Sood

- by paul424

- by Dave

- by dtbarne

- by ScottGu

- by Matt Bishop

- by Rich

- by Dev G

- by magnifico

- by yakatz

- by Anicho

- by greg

- by hugemeow

- by soroosh.strife

- by jasonspiro

- by nomount

- by Yougotiger

- by MidnightLightning

- by AX1

- by Alex

< Previous Page | 264 265 266 267 268 269 270 271 272 273 274 275 | Next Page >