SSIS Error Logging and Notification

I recently spoke at SQL Saturday #192 in Tampa and used this package as a demonstration.  I have made it available for download here.

SSIS comes with a surplus of built in tools and features for error logging and reporting, but once a package is set free in the wild and executed within a SQL Server agent job, or other scheduling process the ability to receive detailed and timely error information becomes a bit trickier. Despite all of the built in features there is no out of the box solution that provides email notification containing detailed error information of a running package.

Some years ago I found a gem that detailed how to modify a package to record errors within a package, using event handlers and script components, and send the error information once the package completes using a send mail task. The post details the steps necessary and gives a great deal of insight into errors within SSIS, http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/, Thank youJohn Welch!!!

I don’t wish to ruin the ending of John’s technique, but the premise of his solution utilizes script tasks in both the OnError and OnPostExecute event handlers and send mail task to send the error information. After utilizing this method for quite some time there were two enhancements that I preferred, which are ENTIRELY personal preference. The first is formatting the message in an easy to read arrangement and the second is to insure I receive only a single message with all the error information that was raised within the package.

My first preference, to receive a well formatted email, is straight forward and would appear quite easy with the out of the box tasks in SSIS, but unfortunately the Send Mail task does not support html messages, which somewhat limits formatting the message body. Anyone who knows me is aware of my inability, as well as desire, to create anything aesthetically pleasing so this wish may seem out of character for me. Despite my visual design limitations in order to be able to quickly respond to an error the information should be laid out in an easy to read and well defined format.

My second preference seemed very straight forward and easy to meet, or so I thought. After modifying some of my packages adding the script and send mail tasks in event handlers I discovered that if an error occurred I would receive numerous emails. This completely perplexed me and it was only as I began to step through the process that I realized I had completely written off event propagation. Each executable has its own associated event handlers. Each event propagates up through the package, by default, unless the “Propagate” variable is set to “False”. An example of propagation can be demonstrated with an execute SQL task within a sequence container. If an error occurs within the execute SQL task it will propagate up to the sequence container, which in turn propagates up to the package. The result is that using event handlers to compile and email error messages can result in numerous emails being sent unless careful attention is paid to setting the “Propagate” variable to “False” in all tasks and event handlers that will suppress multiple executions. The issue that I run into is that in complex packages with multiple executable and containers it can be quite cumbersome and time consuming to create script tasks to suppress unwanted propagation. **If you are not familiar with propagation Jamie Thomson has an amazing video that steps through event handler propagation here.

I began to develop a means of capturing package errors that I would later send in a defined format. After experimenting with event handlers to collect error information I realized that there was an easier way that is built into SSIS. All packages support configuring log providers, which are used to capture events from within a package. Of the log providers available in SSIS, text file, SQL server profiler, SQL Server, Windows event log, and XML file, I decided to use SQL Server to store OnError events. Under the hood logging uses the same method of recording errors as I mentioned above, using the OnError event handler, which means that a single error can be recorded multiple times due to propagation. The reason that I chose this method over capturing error information within a package in a variable is primarily due to ease as it allows to act on the stored information within a single script task as the last task in a package, rather than requiring multiple script tasks in all associated OnError event handlers for all tasks and containers.

With the error information being stored it is time to address the aesthetics of the error email. As I noted the send mail task does not support sending an html formatted message, which limits the ability to design an easily readable presentation. As such I decided to use a script task to retrieve the error information, create an html formatted message using the error details, and send the email usin the System.NET.Mail namespace. I defined the format of the email as illustrated in the example below.

ERROR OCCURRED IN PACKAGE
Task Error Occurred Error Code Error Message
**cntn_Errors – 1073450974 SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “pkeyErrorTable” (2) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
**cntn_Errors – 1071636471 SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK__pkerror__357D0D3EB66D974B’. Cannot insert duplicate key in object ‘dbo.pkerror’. The duplicate key value is (1).”.
**cntn_Errors – 1071607767 SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “pkeyErrorTable.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “pkeyErrorTable.Inputs[OLE DB Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DuplicateKey -1073450974 SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “pkeyErrorTable” (2) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
DuplicateKey -1071636471 SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK__pkerror__357D0D3EB66D974B’. Cannot insert duplicate key in object ‘dbo.pkerror’. The duplicate key value is (1).”.
DuplicateKey -1071607767 SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “pkeyErrorTable.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “pkeyErrorTable.Inputs[OLE DB Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
**ErrorLogging – 1073450974 SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “pkeyErrorTable” (2) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
**ErrorLogging – 1071636471 SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘PK__pkerror__357D0D3EB66D974B’. Cannot insert duplicate key in object ‘dbo.pkerror’. The duplicate key value is (1).”.
**ErrorLogging – 1071607767 SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “pkeyErrorTable.Inputs[OLE DB Destination Input]” failed because error code 0xC020907B occurred, and the error row disposition on “pkeyErrorTable.Inputs[OLE DB Destination Input]” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

**Red denotes error is at the package level and could have “bubbled up” from the calling task

As I have now provided a means of storing the error information and defined a format to present the information I can move forward with my script task development. The script task needs to define three readonly variables, System::ExecutionInstanceGUID, System::TaskID, and System::PackageID which will be used in the body of the script. I will provide the body of the script and then step through and describe what it is doing.

Imports System.Data.SQLClient

Imports System.NET.Mail
Imports System.IO  Public Sub Main()
Dim msg As StringBuilder = New StringBuilder
Try
msg.Append
(“<HTML><Body><table border=””1″”>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<tr>”)
msg.Append(“<td colspan=3><strong>ERROR OCCURRED IN PACKAGE<strong></td></tr>”)
msg.Append(“<tr>”)
msg.Append(“<th><Strong>Task Error Occurred</Strong></th>”)
msg.Append(“<th><Strong>Error Code      </Strong></th>”)
msg.Append(“<th><Strong>Error Message</Strong></th>”)
msg.Append(“</tr>”)

CreateMessate(msg)
Catch ex As Exception
msg.Append
(“<tr>”)
msg.Append(“<td>Generate Error Message</td>”)
msg.Append(“<td width=100>0</td>”)
msg.Append(“<td>” &amp; ex.Message.ToString &amp; ex.InnerException.ToString &amp; “</td>”)
msg.Append(“</tr>”)
End Try
msg.Append
(“</Table><font size=””1″” color=””red””>**Red denotes error is at the container or package level and could have ” &amp; _
“””<em>bubbled up</em>”” from the calling task</font></Body></HTML>”)

SendMessage(msg)

Dts.TaskResult = ScriptResults.Success
End Sub

Private Sub CreateMessate(ByVal msg As StringBuilderDim src As String = Dts.Variables(“PackageID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”)
Dim cmd As SqlCommand
Dim cmdtext As String
cmdtext = “SELECT sourceid, source, datacode, message FROM sysssislog WHERE event = ‘OnError’ AND executionid = ‘” &amp; _
Dts.Variables
(“ExecutionInstanceGUID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”) &amp; “‘” &amp; _
“ORDER BY source, datacode”

Dim cn As New SqlConnection(“Server=localhost; Database=Adventureworks2012;Integrated Security=SSPI”)
cmd = New SqlCommand(cmdtext.ToString, cn)

cn.Open()
Dim dr As SqlDataReader
dr
= cmd.ExecuteReader
While dr.Read

If src.ToUpper = dr.Item(0).ToString.ToUpper Or dr.Item(1).ToString Like “cntn_*” Then
msg.Append(“<tr>”)
msg.Append(“<td><font color=””red””><em>**” &amp; dr.Item(1).ToString &amp; “</em></font></td>”)
msg.Append(“<td width=100><font color=””red””>” &amp; dr.Item(2).ToString &amp; “</font></td>”)
msg.Append(“<td><font color=””red””>” &amp; dr.Item(3).ToString &amp; “</font></td>”)
msg.Append(“</tr>”)
Else
msg.Append(“<tr>”)
msg.Append(“<td>” &amp; dr.Item(1).ToString &amp; “</td>”)
msg.Append(“<td width=100>” &amp; dr.Item(2).ToString &amp; “</td>”)
msg.Append(“<td>” &amp; dr.Item(3).ToString &amp; “</td>”)
msg.Append(“</tr>”)
End If

End While
If
cn.State <> ConnectionState.Closed Then
cn.Close()
cn.Dispose()
cmd.Dispose()
End If

End Sub

Public Sub SendMessage(ByVal msg As StringBuilder)
Try
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.SSend(varHTMLMail)

Catch ex As Exception
Dim err As StringBuilder = New StringBuilder
err.Append
(“<html>”)
err.Append(“<body>”)
err.Append(“<table border=””1″”>”)
err.Append(“<tr>”)
err.Append(“<td colspan=2><strong>ERROR OCCURRED IN GENERATE ERROR MESSAGE TASK/SENDMESSAGE SUB<strong></td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Message: </td>”)
err.Append(“<td>” &amp; ex.Message.ToString &amp; “</td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Inner Message: </td>”)
err.Append(“<td>” &amp; ex.InnerException.ToString &amp; “</td>”)
err.Append(“</table>”)
err.Append(“</Body>”)
err.Append(“</html>”)

Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.Send(varHTMLMail)

End Try
End Sub

The first step is to instantiate the variable msg that will be used to hold the error message formatted in html, I used stringbuilder as the data type rather than string for performance sake as this variable is constantly appended to. Then within Ty…Catch the msg is appended with html to begin the message and format the table:

Dim msg As StringBuilder = New StringBuilder
Try
msg.Append
(“<HTML><Body><table border=””1″”>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<col align=””Center””>”)
msg.Append(“<tr>”)
msg.Append(“<td colspan=3><strong>ERROR OCCURRED IN PACKAGE<strong></td></tr>”)
msg.Append(“<tr>”)
msg.Append(“<th><Strong>Task Error Occurred</Strong></th>”)
msg.Append(“<th><Strong>Error Code      </Strong></th>”)
msg.Append(“<th><Strong>Error Message</Strong></th>”)
msg.Append(“</tr>”)

The message format has been initiated so it is now time to retrieve the error information from the log table.  The CreateMessage Sub accepts the msg variable to append the error information to the message body formatted in html.  The CreateMessage uses a DataReader to extract the error information from the sysssislog table which is filtered with the OnError event and uses the read only variable System::ExecutionInstanceGUID to insure that only error information for the specific execution is returned.  The DataReader iterates through the results placing the results in the specific html table columns.  During each iteration the values are evaluated to see if the current row is the result of event propagation.  If the value is ascertained to have been captured as a possible result of propagation then it is formatted in red.

Private Sub CreateMessate(ByVal msg As StringBuilderDim src As String = Dts.Variables(“PackageID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”)
Dim cmd As SqlCommand
Dim cmdtext As String
cmdtext = “SELECT sourceid, source, datacode, message FROM sysssislog WHERE event = ‘OnError’ AND executionid = ‘” &amp; _
Dts.Variables
(“ExecutionInstanceGUID”).Value.ToString.Replace(“{“, “”).Replace(“}”, “”) &amp; “‘” &amp; _
“ORDER BY source, datacode”

Dim cn As New SqlConnection(“Server=localhost; Database=Adventureworks2012;Integrated Security=SSPI”)
cmd = New SqlCommand(cmdtext.ToString, cn)

cn.Open()
Dim dr As SqlDataReader
dr
= cmd.ExecuteReader
While dr.Read

If src.ToUpper = dr.Item(0).ToString.ToUpper Or dr.Item(1).ToString Like “cntn_*” Then
msg.Append(“<tr>”)
msg.Append(“<td><font color=””red””><em>**” &amp; dr.Item(1).ToString &amp; “</em></font></td>”)
msg.Append(“<td width=100><font color=””red””>” &amp; dr.Item(2).ToString &amp; “</font></td>”)
msg.Append(“<td><font color=””red””>” &amp; dr.Item(3).ToString &amp; “</font></td>”)
msg.Append(“</tr>”)
Else
msg.Append(“<tr>”)
msg.Append(“<td>” &amp; dr.Item(1).ToString &amp; “</td>”)
msg.Append(“<td width=100>” &amp; dr.Item(2).ToString &amp; “</td>”)
msg.Append(“<td>” &amp; dr.Item(3).ToString &amp; “</td>”)
msg.Append(“</tr>”)
End If

End While
If
cn.State <> ConnectionState.Closed Then
cn.Close()
cn.Dispose()
cmd.Dispose()
End If

End Sub

Execution returns back to the Public Main and the Catch block is used to capture any errors that occurred in the script which includes the script error information.  The message body appends the note denoting format for possible propagated error messages and the html code is included to close the table, body, and html tags.

If an error does not occur then message body appends the note denoting format for possible propagated error messages and the html code is included to close the table, body, and html tags.  The script then calls the SendMessage sub passing the full html formatted error message.

The SendMessage sub uses the System.Net.Mail namespace to send the html formatted message.  Structured error handling is included within the sub procedure and if an error is encountered it will be added to the msg body.  I haven’t quite completed the logic to dynamically handle failure at this point, but had considered writing the error message to a known file location.

Public Sub SendMessage(ByVal msg As StringBuilder)
Try
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.SSend(varHTMLMail)

Catch ex As Exception
Dim err As StringBuilder = New StringBuilder
err.Append
(“<html>”)
err.Append(“<body>”)
err.Append(“<table border=””1″”>”)
err.Append(“<tr>”)
err.Append(“<td colspan=2><strong>ERROR OCCURRED IN GENERATE ERROR MESSAGE TASK/SENDMESSAGE SUB<strong></td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Message: </td>”)
err.Append(“<td>” &amp; ex.Message.ToString &amp; “</td>”)
err.Append(“</tr>”)
err.Append(“<td>Erro Inner Message: </td>”)
err.Append(“<td>” &amp; ex.InnerException.ToString &amp; “</td>”)
err.Append(“</table>”)
err.Append(“</Body>”)
err.Append(“</html>”)

Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As String = String.Empty
Dim varAddresses As String = String.Empty

varMailBody = msg.ToString

varAddresses = “Error@Error.com”
varHTMLMail = New MailMessage(“Error@Error.com”, varAddresses, “SSIS Error Messenger”, varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient(“localhost”)
varSMTPClient.UseDefaultCredentials = True

varSMTPClient.Send(varHTMLMail)

End Try
End Sub

For brevity’s sake I have not included screen shots of the different tasks and steps or the C# code, but will email the example packages and/or code if you request them.  Email me at sqlsafety@gmail.com for the samples.

The sample package can be downloaded from my site here.

SSISEndtoEnd

Comment ( 1 )

Leave a Reply


Hit Counter provided by Sign Holders