How to use SSIS XML Task to differentiate between XML Files with examples? The screenshot below shows you the data inside the books.xml file in the system Destination folder.
The following screenshot will show you another XML file we will 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. Please. refer to the below links for further understanding of XML Task.
DiffAlgorithm: The 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 with the same local name but a different namespace are considered 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. If we set this option to true, two elements with 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 prefixes of element and attribute names or not. If we set this option to true, two elements with the same local name but a different prefix are considered 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. Suppose 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. We must set the SaveDiffGram property to TRUE to save the differences 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, Please select the DiffGramDestinationType as File Connection.
- If you want to store the XML differences in the Variable, Please select the DiffGramDestinationType as the Variable and select the variable name.
In this example, we will 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 create a new XML file to store the differences. So, we selected the 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 use the XML file present in the local file system, Please select the Source Connection as File Connection.
- If you stored the Source Connection in the Variable, select the Source Connection as a Variable and select the variable name.
- And, If you want to input the XML data directly, Please select the SourceType property as Direct Input.
In this example, we use 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. We haven’t created any connection Manager before, so we select <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 chose the Existing File option from the Usage Type.
Click 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.
We have to configure the Second XML file, so please select the SecondOperandType property.
- If you use the XML file present in the local file system, Please select the SecondOperandType as File Connection.
- If you stored the XML file in the Variable, select the SecondOperandType as Variable and select the variable name.
- And, If you want to input the XML data directly, Please select the SecondOperandType property as Direct Input.
In this example, we use 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. The screenshot below shows 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.
