SSIS XML Task to differentiate between XML Files

How to use SSIS XML Task to differentiate between XML Files with example?. The Below screenshot shows you the data inside that books.xml file present in the system Destination folder.

SSIS XML Task to differentiate between XML Files 1

The following screenshot will show you another XML file that we are going to use in this example. Our SSIS task is to find the differences between books.xml and books2.xml XML files

SSIS XML Task to differentiate between XML Files 2

SSIS XML Task to differentiate between XML Files

To differentiate between XML Files, First Drag and drop the XML Task into the Control Flow region and rename it as SSIS XML Task to differentiate between XML Files

SSIS XML Task to differentiate between XML Files 3

Double click on it will open the XML Task Editor to configure it.

In this SSIS example, We are finding the differences between the XML files, so Please change the operationType property to Diff

SSIS XML Task to differentiate between XML Files 4

DiffAlgorithm: SSIS XML task provides three different options for this property. The Diff operation can be organized to use different comparison algorithm depending on whether the comparison must be fast or precise. It has one more option called Auto, and if you don’t know which one to select, choose the AUTO option.

TIP: The Auto option will select either precise or fast comparison based on the size of the compare documents.

SSIS XML Task to differentiate between XML Files 5

DiffOptions: The Diff Operation includes a set of options to customize the XML comparison. The following table describes the list of available options, and every property has two options: True and False. If we set the property to TRUE, DiffOption ignores comparing the property.

If we set this option to true, two elements that have the same local name, but a different namespace are considered to be identical.

DiffOptionDescription
IgnoreCommentsPlease specify whether you want to compare the comment nodes or not.
IgnoreNamespacesPlease specify whether you want to compare the namespace URI (uniform resource identifier) of an element and its attribute names or not.
IgnorePrefixesPlease specify whether you 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.
IgnoreXMLDeclarationPlease specify whether you want to compare the XML declarations or not.
IgnoreOrderOfChildElementsPlease specify whether you want to compare the order of child elements or not. If we set this option to true, child elements that differ only in their position in a list of siblings are considered to be identical.
IgnoreWhiteSpacesPlease specify whether the white spaces compared or not.
IgnoreProcessingInstructionsPlease specify whether you want to compare the processing instructions or not.
IgnoreDTDPlease specify whether you want to ignore the DTD or not.
SSIS XML Task to differentiate between XML Files 6

FailOnDifference: This property has two options: True and False. If we set this property to TRUE. If there are any XML differences, SSIS XML Task will fail the task. For now, we are changing the property to TRUE

SSIS XML Task to differentiate between XML Files 7

In this example, let us store the XML difference output in an XML file. To save the differences, we have to set the SaveDiffGram property to TRUE and configure the DiffGram destination.

SSIS XML Task to differentiate between XML Files 8

Let us configure the Destination path where you want to store the differences by selecting the DiffGramSave property.

  • If you want to store the XML differences in XML file present in the local file system then, Please select the DiffGramDestinationType as File Connection.
  • If you want to store the XML differences in the Variable then, Please select the DiffGramDestinationType as Variable and select the variable name.

In this example, we are going to store the XML differences in the XML_Diff.xml file. So, we are selecting the DiffGramDestinationType property as a File Connection.

SSIS XML Task to differentiate between XML Files 9

If you already created the File Connection Manager before, please select the previously created one. Here, We haven’t created any connection Manager before so, We are selecting <New Connection..>.

SSIS XML Task to differentiate between XML Files 10

Once you click on the <New Connection..> option, File Connection Manager Editor will be opened to configure it. In this example, we are creating a new XML file to store the differences. So, we were selecting Create File option from the Usage Type and created the XML_Diff.xml file inside the Destination Folder.

SSIS XML Task to differentiate between XML Files 11

Let us configure the Source Connection by selecting the SourceType property.

  • If you are using the XML file present in the local file system then, Please select the Source Connection as File Connection.
  • If you stored the Source Connection in the Variable then, Please select the Source Connection as Variable and select the variable name.
  • And, If you want to directly input the XML data then, Please select the SourceType property as Direct Input.

In this example, we are using the above specified books.xml file as a source. So, we are selecting the SourceType property as a File Connection.

SSIS XML Task to differentiate between XML Files 12

If you already created the File Connection Manager before, please select the already created one. Here, We haven’t created any connection Manager before so, We are selecting <New Connection..>.

SSIS XML Task to differentiate between XML Files 13

Once you click on the <New Connection..> option, File Connection Manager Editor will be opened to configure it. In this example, we are selecting the above-specified XML file. So we are choosing the Existing File option from the Usage Type.

SSIS XML Task to differentiate between XML Files 14

Click on the Browse button to select the Existing File from our file system. From the below screenshot you can observe that, We selected the books.xml file inside the Destination Folder

SSIS XML Task to differentiate between XML Files 15

Now we have to configure the Second XML file, so please select the SecondOperandType property.

  • If you are using the XML file present in the local file system then, Please select the SecondOperandType as File Connection.
  • If you stored the XML file in the Variable then, Please select the SecondOperandType as Variable and select the variable name.
  • And, If you want to directly input the XML data then, Please select the SecondOperandType property as Direct Input.

In this example, we are using the above-specified books2.xml file as the second XML file. So, we are selecting the SecondOperandType property as a File Connection.

SSIS XML Task to differentiate between XML Files 16

If you already created the File Connection Manager before, please select the previously created one. Here, We haven’t created any connection Manager back. So, we are selecting <New Connection..> option beside the SecondOperand property.

SSIS XML Task to differentiate between XML Files 17

From the below screenshot, you can observe that we selected the books2.xml file inside the Destination Folder

SSIS XML Task to differentiate between XML Files 18

Click OK to finish configuring the SSIS XML Task to differentiate between the XML Files package.

SSIS XML Task to differentiate between XML Files 19

Let’s run and see whether there are any differences between the books.xml file and books2.xml file. From the below screenshot, you can observe that our SSIS XML Task to differentiate between the XML Files package has failed. It means there are differences between both the XML files

SSIS XML Task to differentiate between XML Files 20

Let’s open the XML_Diff.xml file to see the differences between both the XML files.

SSIS XML Task to differentiate between XML Files 21