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 string
s 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:
Dim XML As New StringBuilder()
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"">")
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
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:
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:
="1.0"="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:
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
- MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server V11 - Business Intelligence Development and Maintenance
- Designing and implementing Packages Tutorial
- SSIS Tutorial: Creating a Simple ETL Package
- XSLT Usage Tutorial