How to use SSIS XML Task to differentiate between XML Files with examples? The Below screenshot shows you the data inside that books.xml file present in the system Destination folder.
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
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
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
DiffAlgorithm: SSIS XML task provides three different options for this property. The Diff operation can be organized to use different comparison algorithms 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 compared documents.
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.
| DiffOption | Description |
|---|---|
| IgnoreComments | Please specify whether you want to compare the comment nodes or not. |
| IgnoreNamespaces | Please specify whether you want to compare the namespace URI (uniform resource identifier) of an element and its attribute names or not. |
| IgnorePrefixes | Please 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. |
| IgnoreXMLDeclaration | Please specify whether you want to compare the XML declarations or not. |
| IgnoreOrderOfChildElements | Please 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. |
| IgnoreWhiteSpaces | Please specify whether the white spaces are compared or not. |
| IgnoreProcessingInstructions | Please specify whether you want to compare the processing instructions or not. |
| IgnoreDTD | Please specify whether you want to ignore the DTD or not. |
FailOnDifference: This property has two options: True and False. If we set this property to TRUE. If there are any XML differences, the SSIS XML Task will fail the task. For now, we are changing the property to TRUE
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.
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 the 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 the 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.
If you have 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..>.
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 selected Create File option from the Usage Type and created the XML_Diff.xml file inside the Destination Folder.
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 a 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.
If you have 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..>.
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.
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
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.
If you have 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 the <New Connection..> option beside the SecondOperand property.
From the below screenshot, you can observe that we selected the books2.xml file inside the Destination Folder
Click OK to finish configuring the SSIS XML Task to differentiate between the XML Files package.
Let’s run and see whether there are any differences between the books.xml file and the 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
Let’s open the XML_Diff.xml file to see the differences between both XML files.
