SSIS 2005 XML Validation Failure Logging

Question

I am working on implementing an SSIS package in SQL Server 2005 which deals with XML files.

Multiple XML files will be read-in from a directory on the file system and data from each XML file will be imported into a database table. The XML to database row works fine and I can iterate over all *.xml files in a specific directory.

I have run into an instance during development when the XML file does not validate against the XSD provided. When this happens the package halts execution. I would like to log the invalid XML filename (and maybe the XML file contents as well) to an error log table in SQL Server and then CONTINUE processing the remaining XML files in the directory.

Has anyone ever done this before? I will also be implementing error logging for the XML data itself so if it can be incorporated into this, I would be open to that idea as well.

Thanks!

Answer 1

I haven't dealt with xml like this in particular, but I can suggest some places to start.

You could place an action in the Event Handlers Tab to handle the error. If you have the the filename and contents in a variable, you can insert that into a table.

I was thinking you could use the error route out of your datasource in a dataflow, but the error probably happens before that.