Validate the XML file using XML task in SSIS

 Using the XML task we can validate our XML file is correct or not using the Schema definition (XSD). An XSD document contains the tab, Attribute definition of the XML file.

We have an XML file 

XSD file  

It validates the XML file with XSD file if the file is correct in this case it will return TRUE else FALSE. We can either store this information in the file or we can store this result into the variable.

Let’s see how we validate this file.

Selecting the Operation Type as Validate.  

And creating the connection for the Source Type as file connection. Connection for the XML File which we are going to validate.

Select the output option. 

If we want to save the validation report in the file we need to select this option as TRUE.

We can save this results in either file or a variable.

 

Here we are saving the result in the File. We need to create the file connection.

 

For the XSD file we can provide the File or we can directly write the XSD in the input window.

  

In case of direct input we need to write the XSD in the Source Editor.

 

For variable, we need to set this in any variable.

Here we are using the file connection.

 

We will set the validation option

  

If we want to fail our package if XML file validation is failed. In this case, we need to set this option as true.

Now we are ready to run this task. 

Executed successfully.

See the result. 

Here we have set the validation details true then it provides the all information. If we set false then it will return as True or False only.

Setting False.  

Running this task. 

Executed successfully.

See the result. 

Now we are editing the XML file and putting some garbage contain and validating that with the XSD File.

 

This XML is an invalidate XML.

Running the package and see the result.

  

Suppose we want to fail the package if we have an invalid XML file. In this case, we need to set the

FailOnValidationFail property as True.

 

Let’s run our package.

Package get failed. 

Because our XML file is invalid.

Below is the error message

[XML Task] Warning: Task failed to validate "A validation error occurred when validating the instance document:  Line 16, Column 24: The element 'student' cannot contain text. List of possible elements expected: 'id, branch, cgpa'. ".

 We can save the result into the variable also.

Creating a variable 

To store the result.

  

Now taking Script task to see the variable result.

Note:  we have correct the file.

Script task code

public void Main()                                 {                                                 // TODO: Add your code here             MessageBox.Show("XML file validation result : " + Dts.Variables["User::File_validation_Result"].Value.ToString());                                                 Dts.TaskResult = (int)ScriptResults.Success;                                 }

Now running the package.

  

Hope this will help to validate the XML file.