Error Summary Email with XSLT and XML in a SSIS Package

Carlos Bocanegra

0.00/5 (No votes)

23 Sep 2015CPOL3 min read

10.2K

This tip describes a useful way to create and send an email with a summary of the exceptions from a file validation process in a SSIS package using a XML and a XSLT template.

Introduction

This tip describes a useful way to create and send an email with a summary of exceptions from a file validation process in a SSIS package using a XML and a XSLT template.

Scenario

Suppose in this scenario, you are requested to create a SSIS package that executes some validations on some files and send the results to the user in a summarized email every time the package runs. This email must use a defined template that can be changed if it’s necessary.

Solution

Validation Process

Suppose this is the validation process we are using, we have some files in a repository and we have a foreach container to go through the files. For each file three validations will be done, if any validations are not passed, the exception will be saved to a list of exceptions:

To save the exception list, we will use a variable type Object where we will save a List of Strings (Generic List). Each item in the list will be an array of properties (i.e. Message, File Name and Directory Name) joined with pipe (“|”) in a single line. The string list would look like this:

The file extension is not valid|FileName465.unknown|C:\FileValidation\Repository
The file name is not valid|FileName14.txt|C:\FileValidation\Repository
The file is too big|FileName882.txt|C:\FileValidation\Repository

This list of strings will be our exception list that will be available at the end of the validations to be sent to the user.

Create XML

Once we have the exception list saved, we will create an XML of it. This XML will be used to bind the XSLT template.

To create the XML, we will use a String Builder variable where we will create the XML manually. We will use a code like this:

VB.NET

Dim XML As New StringBuilder()

'Header
XML.Append("<?xml version=""1.0"" encoding=""utf-16""?>")
XML.Append("<ArrayOfException ")
XML.Append("xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" ")
XML.Append("xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">")

'Content
For Each Ex As Exception In ExceptionList
XML.Append("<Exception>")
XML.Append(String.Concat("<Message>", Ex.Message, "</Message>"))
XML.Append(String.Concat("<FileName>", Ex.FileName, "</FileName>"))
XML.Append(String.Concat("<FileDirectory>", Ex.FileDirectory, "</FileDirectory>"))
XML.Append("</Exception>")
Next

'Footer
XML.Append("</ArrayOfException>")

Return XML.ToString()

NOTE

The best way to create the XML would be to create a Serializable class, bind it with the information and serialize it using a code like this:

VB.NET
Dim SW As New StringWriter()
Dim Serializer As New XmlSerializer(ExceptionList.GetType())
Serializer.Serialize(SW, ExceptionList)
Return SW.ToString()
This option is not possible in SSIS because when you create the serializer and try to serialize the object, it creates a stream of the object but it loses the reference and throws this exception:

The type initializer for 'Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriterList1' threw an exception. Object reference not set to an instance of an object.

So, the best workaround in this case was to create the XML manually.

Finally, this XML will be saved to a string variable, we will use it after to bind the XSLT template.

Bind XML into the XSLT template (XML Task type XSLT)

Now that we have the XML, we have to create a new XML Task where we will bind the XML into the XSLT to get the email body. But after that, we need a File Connection to the XSLT file that has our template:

For this example, we will use an XSLT file like this for our email body:

XML

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="html" indent="yes"/>
  <xsl:template match="/">
     <html>
       <body style="font-family:Calibri;">
         <span>Exception List:</span>
         <br/>
         <br/>
        
         <table border="1">
           <tr bgcolor="#e0e0e0">
              <th>Message</th>
              <th>File Name</th>
              <th>File Directory</th>
           </tr>

           <xsl:for-each select="ArrayOfException/Exception" >
              <tr>
                <td>
                  <xsl:value-of select="Message"/>
                </td>
                <td style="white-space:nowrap;">
                  <xsl:value-of select="FileName"/>
                </td>
                <td>
                  <xsl:value-of select="FileDirectory"/>
                </td>
              </tr>                                   
           </xsl:for-each>

         </table>
       </body>
     </html>
  </xsl:template>

</xsl:stylesheet>

To create the body of the email using the XSLT template, we will need to create a XML Task that will do the bind for us. We will need to set these properties:

At the end, we should get an HTML like this:

Send Summary Email

Finally, we have the email body, we just have to send the email. To do this, we can use a Send Email task or this code in a Script Task:

VB.NET

Dim Mail As New MailMessage(FromAddress, ToAddress, Subject, Body)
Dim SMTP As New SmtpClient(SMTPHost)
SMTP.Credentials = CredentialCache.DefaultNetworkCredentials
SMTP.Send(Mail)

Conclusion

The XSLT files are very useful when we need to have a customizable template separate from the email sending. But as you could see in this example, we had to create the XML manually, this was because the serialization can’t be done in SSIS. In this case, I recommend you to consider using a console application instead, where we will be able to create a custom class and serialize it to get the XML.

References

License

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