Post reply

XML data does not match XSD but it still processes as successful

  • January 25, 2018 at 5:03 am

    #332855

    Hello

    I have an XML file and a corresponding XSD file
    I have created a very simple SSIS package
    I've added a dataflow task to this package that populates a table from the XML
    Within the dataflow, I'm using an XML Source and an OLE DB Destination

    My XML Source uses an XML file location and points to an XML and XSD location

    In my XSD, using the attribute use, I've defined a number of attibutes as required and a number as optional

    eg.
    <xs:attribute name="Firstname" type="xs:string" use="required"/>
    <xs:attribute name="Surname" type="xs:string" use="required"/>
    <xs:attribute name="Height" type="xs:int" use="optional"/>

    Now, if my XML is missing the required column, it still processes it but puts NULL in the column in my SQL table
    Shouldn't this fail when executing the SSIS package, due to missing required data
    e.g. <PersonDetails Firstname="Bob" Height="190" /> should fail

    It does fail on datatype errors i.e. it's there but there's a datatype mismatch

    e.g. <PersonDetails Name="Bob" Surname="Smith" Height="abc" /> fails as Height is not an int

    Am I doing something wrong OR, within SSIS, do I have to let this run through and perform optional/required checks once read from the source
    If so, what's the point of the use attribute in the XSD

    Thanks

    Damian.

    - Damian

  • January 25, 2018 at 5:16 am

    #1977299

    Use minOccurs
    😎

  • January 25, 2018 at 6:24 am

    #1977306

    Thanks

    So does use="required" actually do anything
    Presumably, I just use minOccurs="1" maxOccurs="1" rather than use="required"

    - Damian

  • January 25, 2018 at 6:48 am

    #1977311

    DamianC - Thursday, January 25, 2018 6:24 AM

    Thanks

    So does use="required" actually do anything
    Presumably, I just use minOccurs="1" maxOccurs="1" rather than use="required"

    Sorry, didn't realize you were talking about an attribute, minOccurs applies to an element.
    Did you look at the Microsoft reference?
    😎

    IIRC, I've seen somewhere that at least in some parser it's been made redundant.

  • January 25, 2018 at 7:09 am

    #1977319

    I have looked at the MS reference and it does look like it's been used correctly
    Doesn't look like this works in SSIS
    Did wonder if there was a setting somewhere but can't see anything obvious
    I think I will read it in and handle it from there just using the data flow for data type and structure comparisons

    Thanks

    Damian.

    - Damian

  • January 25, 2018 at 11:18 am

    #1977404

    Sorry if this is a stupid question - but I didn't see you mention HOW you were performing the validation in SSIS.  In short you need to actually run an XML validation step (which uses the XSD you're importing), but if only works on single-file XSD's as I recall.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 26, 2018 at 1:36 am

    #1977489

    Hello Matt

    I have a DataFlow that's using an XML Source
    Here I specifiy the XML and XSD

    - Damian

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply