XML Task to compare XML against XSD

Question

XML Task to compare XML against XSD

DamianC

SSCrazy Eights

Points: 8023
More actions
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

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

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

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

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.

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

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

DamianC SSCrazy Eights Points: 8023 More actions · Answer 4

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

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.