CODEWITHSUNDEEP
sql-serverxmlssisxsd-validationssis-2012
Ranjith Varatharajan
Ranjith Varatharajan

Reputation: 1694

XML Validation in SSIS

I have a xml file like this

...
<text>
  <font_option>true</font_option>
  <font_size>5</font_size>
</text
...

I need to validate this xml file. If font_option is true, then the font_size is mandatory, if font_option is false, then font_size is optional.

How can I validate this in SSIS using xml task?

Upvotes: 1

Views: 2088

Answers (2)

wp78de
wp78de

Reputation: 18950

There are two ways in SSIS for validating an XML file:

1) XML Task, easy but has some limitations

2) Script Task, more flexible bit requires some C#/.NET knowledge.

Validating XML file against XSD with SSIS

  • If you do not have an XML Schema Definition file you have to create it first, e.g. using the XML Schema Definition Tool.
  • Create a New File Connection for the XML and the XSD file
  • Add an XML Task to your Control Flow (connect it to your Data Flow Task that processes the XML file).
  • Edit the XML Task:
    • select the XML Connection Manager as the input source type and source file
    • and the XSD Connection Manager as the Second Operand
    • OperationType should be Validate
    • ValidationType should be XSD.
  • Test the package: run it with an invalid input to make it fail, and watch the result with valid input.

If you have a very complex XSD it might be necessary to use a script task and validate the XML file using code.

Regarding your question about a conditional constraint: The basic idea of XSD is to bind validation to element types. If you have one set of elements that should validate one way and another set another way, it's pretty clear that deal with two distinct types of elements. The simplest solution would be to make font_size mandatory. However, XSD 1.1 allows conditional required elements.

Upvotes: 2

Mike Fabry
Mike Fabry

Reputation: 71

Define a XSD schema file (How-To here) to the outer bounds of what you expect, and use the XML Task in the Control Flow.

http://msbitutorials.blogspot.com/2013/11/xml-task-in-ssis-with-example.html

Your conditional requirement on the second attribute is odd. If you're trying to constrain the values, you should do so 100% of the time, especially if you're attempting to load to a DB. You could then further route rows once in a data flow task.

Upvotes: 0

Related Questions