Error Handling in SSIS

Question

I have created a SSIS package which gets the XML file from a folder and checks with the schema, if the schema fails, the package logs the error and moves the file to a error folder. Currently, I have done all the requirements, and is working fine except the error message i'm getting at the end of the execution.

1. Validate XML file

2. The error message which I'm getting

3. The error message which I'm getting

The package works fine as expected. How can I suppress this error message?

Update #1:

This is my error history

This is my XML Schema validation task properties.

Answer 1

Suggestions

The issue may be caused by the FailPackageOnFailure and FailParentOnFailure properties. Click on the Validate XML Task and in the Properties Tab change these properties values. Alos in the Control Flow Go to the properties and change the MaximumErrorCount value and make it bigger than 1.

Also you can find other helpful informations in this link:

• Continue Package Execution After Error In SSIS

Workaround using Script Task

1. Add 3 Variables to your package:

   @[User::XmlPath] Type: String, Description: Store the Xml File Path
   @[User:XsdPath] Type: String, Description: Store the Xsd File Path
   @[User:IsValidated] Type: Boolean, Description: Store the result of Xml validation
   

2. Add a script Task, select XmlPath and User:XsdPath as ReadOnly Variables and IsValidated As ReadWrite Variable

3. Set the Script Language to Visual Basic

4. In the Script Editor write the following code (this is the whole script task code)

   #Region "Imports"
   Imports System
   Imports System.Collections.Generic
   Imports System.Data
   Imports System.Math
   Imports System.Text
   Imports System.Xml
   Imports System.Xml.Schema
   Imports Microsoft.SqlServer.Dts.Runtime
   #End Region
   
   <Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>
   <System.CLSCompliantAttribute(False)>
   Partial Public Class ScriptMain
       Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
   
       Enum ScriptResults
           Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
           Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
       End Enum
   
       Public Function LoadXml(xmlFilePath As String, xsdFilePath As String) As Boolean
           Dim settings As New XmlReaderSettings()
           settings.Schemas.Add(Nothing, xsdFilePath)
           settings.ValidationType = ValidationType.Schema
           Dim errorBuilder As New XmlValidationErrorBuilder()
           AddHandler settings.ValidationEventHandler, New ValidationEventHandler(AddressOf errorBuilder.ValidationEventHandler)
           Dim reader As XmlReader = XmlReader.Create(xmlFilePath, settings)
           ' Read the document...
           Dim errorsText As String = errorBuilder.GetErrors()
           If errorsText IsNot Nothing Then
               Return False
           Else
               Return True
           End If
       End Function
   
       Public Sub Main()
   
           Dts.Variables("IsValidated").Value = LoadXml(Dts.Variables("XmlPath").Value.ToString, Dts.Variables("XsdPath").Value.ToString)
   
           Dts.TaskResult = ScriptResults.Success
       End Sub
   
   
   End Class
   
   Public Class XmlValidationErrorBuilder
       Private _errors As New List(Of ValidationEventArgs)()
   
       Public Sub ValidationEventHandler(ByVal sender As Object, ByVal args As ValidationEventArgs)
           If args.Severity = XmlSeverityType.Error Then
               _errors.Add(args)
           End If
       End Sub
   
       Public Function GetErrors() As String
           If _errors.Count <> 0 Then
               Dim builder As New StringBuilder()
               builder.Append("The following ")
               builder.Append(_errors.Count.ToString())
               builder.AppendLine(" error(s) were found while validating the XML document against the XSD:")
               For Each i As ValidationEventArgs In _errors
                   builder.Append("* ")
                   builder.AppendLine(i.Message)
               Next
               Return builder.ToString()
           Else
               Return Nothing
           End If
       End Function
   End Class
   

5. Use Precedence Constraints with expression to manipulate both of Validation success and failure cases

Script Code Reference

• VB.NET validating XML file against XSD file and parsing through the xml