Compare two xml files and find the difference of both file using XML Task in SSIS

Using this option compares the two xml files and we can generate the report which has the different value from source.

For this operation we need to select the Operation Type as Diff.

  

Using this operation we can compare two XML Documents. It use the first XML as Source and compare with the one XML document which we have providing. In the output we will get the Difference of the XML data.

Let’s this demo

We have below file two files with the data

  

Taking XML Task and creating the connection.  

Now selecting the DiffAlgorithma as Auto.

  

Setting FailOnDifference as false. If we select True in this case if there is any difference in the XMl file in this case package get failed.

We want to save the DiffGram so we are setting this property as True.

When we set SaveDiffGram as true then DiffGramSave property will enable otherwise it is disable.

We need to create the connection where we want to save the difference result.

We can save the operation result.

  

This file is having the information of the task operation. Like this task complete successfully or not.

  

Here we need to provide the second XML file which we want to compare.

Now our package is ready to run.

Running this package.

 

Executed successfully.

See the result.

Got the different information in the file.  

In operation result file we will get the below information.

 

If there is no any difference in this file in this case it will be true.

See the below file.

  

Both file have same information. Now running this package.

 

Executed successfully.

Diff result we are not getting anything.

  

And operation result we are getting the below information.   

In the diff there are multiple options

  

Based on the requirement we can set it.

Ignore Comments: by default the value of this property is False. If we set a TRUE then it compares the comment of the both XML file.

Any text between <!-- and --> characters is considered as a comment.

Ignore Namespaces: Please specify whether we want to compare the namespace URI (uniform resource identifier) of an element and its attribute names or not. If we set the value true then XML element with the same local name are considered as identical. It ignores the name space for the both files.

Ignore Prefixes:  we need to specify whether we want to compare the prefixes of element and attribute names or not. If we set this option to true, two elements that have the same local name but a different prefix are considered identical.

Ignore XML Declaration: we need to specify whether we want to compare the XML declarations or not.

Ignore Order of Chile Element: It Ignore the order of child element in the XML document. IF we set this property as True then child element with the different position is considered to be identical.

Ignore white Spaces: It controls the white space while making compare.

Ignore processing Instruction: We specify whether we want to compare the processing instructions or not.

Ignore DTD: we can specify whether we want to compare the Document Type Declaration (DTD) or not.