The SSIS XML Task allows us to perform Six types of Operations on XML files. In this article We will show you, How to use SSIS XML Task to differentiate between XML Files with example. Before we get into the example, let us see the data we have in our local file system Destination folder. Below screenshot shows you the data inside that books.xml file.
Following screenshot will show you another XML file that we are going to use in this example. Our task is to find the differences between books.xml and books2.xml
SSIS XML Task to differentiate between XML Files
In order 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 example, We are finding the differences between the XML file so, Please change the operationType property to Diff as shown in the below screenshot
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 which one to select then you can choose AUTO option.
TIP: Based on the size of the documents being compared, Auto option will select either precise or fast comparison.
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 as two options: True and False. If we set the property to TRUE, DiffOption will ignore 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 then, if there are any XML differences then 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 a XML file. In order 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 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 XML_Diff.xml file. So, we are selecting the DiffGramDestinationType property as File Connection as shown below.
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..>.
Once you click on the <New Connection..> option, File Connection Manager Editor will be opened to configure it. In this example we are creating new XML file to store the differences. Sso we are selecting 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 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 source. So, we are selecting the SourceType property as File Connection.
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..>.
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 selecting 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 second xml file. So, we are selecting the SecondOperandType property as File Connection.
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..> 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 XML Files package.
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 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 the XML files.
Thank You for Visiting Our Blog.
Leave a Reply