none
SSIS+XML+XSD+C# : Detect and log errors

    Question

  • Hi every body,

    While validating my xml file with an .xsd schema, i need to detect all errors in order to load the line of data (or the key) + the error message ...

    then save it into a log file or idealy directly to a database.

    Exemple:

    ID      Name   Sex  Age

    1        Jo         M     L

    2        Sandy   F      18     

    3        Sam     1      30   

    Now, in my .xsd, the sex must be either M or F ... and the Age must be an integer.

    so i need to detect   that both lines 1 and 3 are not good according to my xsd.... however the line 2 is good.

    In my Errors table, i  need to log something like this (or anything else similar:

    1 Age 'the value is not an integer'

    3 Sex  'The value is not in the enum {M,F}'

    Thank you for your help.


    Nacer CREPUQ BI Developer

    Thursday, February 14, 2013 6:43 PM

All replies

  • If the incoming XML is in non-compliance to XSD it would abort the package execution or you need to ignore errors and keep processing handling the error in an Event Handler, but the XML source does not capture the specifics you mentioned.

    I suggest you use an XML parser outside the package or inside a Script Task using pure code and .Net XML classes. You then log the results as you wish.


    Arthur My Blog

    Thursday, February 14, 2013 7:06 PM
  • Thank you arthur ...

    you said :  ... inside a Script Task using pure code and .Net XML classes. You then log the results as you wish.

    This is exactly what i was trying to do. I wasn't able to find a .net script on the internet. can you recommand me one ?

    Thank you


    Nacer CREPUQ BI Developer

    Thursday, February 14, 2013 7:12 PM
  • Arthur's idea works by validating in the data flow.  The code below is used to validate against an XML Schema file.

    This code is based on http://msdn.microsoft.com/en-us/library/bb387037.aspx.  This is not yet fleshed out and it writes to the Console (which doesn't exist in the SSIS package).  The function that is defined as an argument of the Validate method (the (o, e) => etc) is going to fire everytime an error occurs.  My thought is to add information for each node that fails the validation.  You could get the ID for the node and the error.  I just get the error and put it into a List<string>.  Next you can iterate through that list and do something.  If you use this in a Script component (not task) which is part of the data flow, you could use this as a Data Source and create an output into which you could send each error as it is found. 

    using System.Xml.Linq;
    using System.Xml;
    using System.Xml.Schema;
    using System.IO; 
    static void Main(string[] args)
            {
                List<string> errorList = new List<string>();
                XmlSchemaSet schemas = new XmlSchemaSet();
                schemas.Add("", XmlReader.Create(new StreamReader(@"XMLData\XMLData\File.xsd")));
    
                XDocument doc1 = XDocument.Load(@"\XMLData\XMLData\DataElement.xml");
                Console.WriteLine("Validating doc1");
                bool errors = false;
                doc1.Validate(schemas, (o, e) =>
                {
                    Console.WriteLine("{0}", e.Message);
                    errorList.Add(e.Message);
                    errors = true;
                });
                Console.WriteLine("doc1 {0}", errors ? "did not validate" : "validated");
    
                Console.WriteLine();
    
                foreach (string item in errorList)
                {
     
                    // Do something with the error
                    Console.WriteLine("Nother error {0}", item);
                }
                Console.ReadLine();
    }

    <?xml version="1.0" encoding="utf-8" ?>
    <Root>
      <row ID="1" Name="Jo" Sex="M" Age="L"/>
      <row ID="2" Name="Sandy" Sex="F" Age="18"/>
      <row ID="3" Name="Sam" Sex="1" Age="39"/>
    </Root>

    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema id="File"
        elementFormDefault="qualified"
        xmlns:mstns="http://tempuri.org/File.xsd"
        xmlns:xs="http://www.w3.org/2001/XMLSchema"
    >
      <xs:element name='Root'>
        <xs:complexType>
          <xs:sequence>
            <xs:element name='row' minOccurs='1' maxOccurs='unbounded'>
              <xs:complexType>
                <xs:attribute name='ID' type='xs:int'/>
                <xs:attribute name='Name' type='xs:string'/>
                <xs:attribute name='Sex'>
                  <xs:simpleType >
                    <xs:restriction base='xs:string'>
                      <xs:enumeration value='M' />
                      <xs:enumeration value='F' />
                    </xs:restriction>
                  </xs:simpleType>
                </xs:attribute >
                <xs:attribute name='Age' type='xs:int'/>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com


    • Edited by Russ Loski Thursday, February 14, 2013 8:45 PM Spelled name wrong
    Thursday, February 14, 2013 7:39 PM
  • Thank you very much Russel,

    i used this code to show somme data :

    MessageBox

            }

    .Show(args.Severity.ToString() + " *** "+ args.Exception.ToString() + " $$$ "+ args.Exception.LineNumber.ToString() + " ### "+ args.Exception.LinePosition.ToString());

    Here is the result for both errors (see the 2 screen shots bellow...sorry it is in french) ...

    however this is not enough. i need :

    1. the key (1 and 3) 

    2. The name of the attribut (Age and Sex)

    3. The value (L and 1)

    Your help is much appreciated.

    Thank you


    Nacer CREPUQ BI Developer

    I updated my code as bellow ... the SourceObject is supposed to get the xml node that caused the exception. However my problem is that SourceObject is always null.

           

    public void ValidationEventHandler(object sender, ValidationEventArgs args)

            { 

    XmlSchemaValidationException ex = (XmlSchemaValidationException) args.Exception;

    XmlNode node = (XmlNode)ex.SourceObject;

    • Edited by NacerCREPUQ Thursday, February 14, 2013 10:49 PM
    Thursday, February 14, 2013 9:10 PM