Post reply

XML Task to compare XML against XSD

  • April 18, 2018 at 5:45 am

    #403273

    Hello

    Using 2008 R2

    I'm trying to add a check to see whether an XML file conforms to its corresponding XSD.

    Basically, I'm added an XML Task
    The source is my xml file, the second operand is my xsd and fail on validation is set to True

    Altough mines not the same, a fairly basic example could be found here: http://microsoft-ssis.blogspot.co.uk/2012/08/validating-xml-file-against-xsd-with.html

    The issue I have is that when it fails, the job fails
    I really want it to succeed but error (if that makes sense)

    One method I have tried is:

    Create a variable that acts as an error counter
    Create an 'OnError' event against the XML Task
    Pass and increment when OnError runs
    Set the XML Task ForceExecutionResult to True
    Objective been that it should succeed but increment the error counter so I can use this in the subsequent presedance constraint

    Basically, this passes through and does nothing other than say it's a success
    When I debug it, it looks like it's not even executing the OnError event

    Where am I going wrong here as i can't get this to work no matter what I try

    Thanks

    Damian.

    - Damian

  • April 18, 2018 at 8:03 am

    #1987323

    You can't have it both ways. If you force the execution result, then the OnError event will of course not fire. If you want the SQL job to succeed, I'd recommend that you configure the advanced properties of the step to report success even if it fails.

  • April 19, 2018 at 1:42 am

    #1987455

    Problem seems to be, forcing success reults in success
    All I (think?) really need to do is, in some way flag it as having failed but continue successfully such that I can use the flag

    I can actually get this to work when running a package through debug as this can take a run upon failure

    It then fails when running in a job as it sees the failure and simply fails the job
    As a result, I can't distingish between a genuine failure and a controlled failure

    I just seem to be going round in circles with this one

    Can't help feeling I'm missing something obvious as I would have thought this was basic ETL functionality

    - Damian

  • April 19, 2018 at 2:08 am

    #1987457

    You can do the schema validation in SQL Server, have a look at this article.
    😎

  • April 19, 2018 at 2:29 am

    #1987460

    Just had an initial glance and that looks like an interesting approach
    So, I could create a schema and compare the XML to the schema using TRY, CATCH
    TRY, CATCH can set the necessary

    Add this as a SQL Task and ensure this is successful

    Then use result generated by TRY, CATCH to determine the flow

    Thanks Eirikur!

    Damian.

    - Damian

  • April 19, 2018 at 3:14 am

    #1987466

    DamianC - Thursday, April 19, 2018 2:29 AM

    Just had an initial glance and that looks like an interesting approach
    So, I could create a schema and compare the XML to the schema using TRY, CATCH
    TRY, CATCH can set the necessary

    Add this as a SQL Task and ensure this is successful

    Then use result generated by TRY, CATCH to determine the flow

    Thanks Eirikur!

    Damian.

    You are very welcome.
    😎

    The article code is a working example and should get you started.

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

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