Tutorial Gateway

  • C Language
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • Js

SSIS XML Task to differentiate between XML Files

by suresh

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.

SSIS XML Task to differentiate between XML Files 1

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 2

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

SSIS XML Task to differentiate between XML Files 3

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

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 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.

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 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.

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 are 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 then, if there are any XML differences then 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 a XML file. In order 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 XML_Diff.xml file. So, we are selecting the DiffGramDestinationType property as File Connection as shown below.

SSIS XML Task to differentiate between XML Files 9

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 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 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.

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 source. So, we are selecting the SourceType property as 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 selecting 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 second xml file. So, we are selecting the SecondOperandType property as File Connection.

SSIS XML Task to differentiate between XML Files 16

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.

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 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 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

Thank You for Visiting Our Blog.

Placed Under: SSIS

Stay in Touch!

Sign Up to receive Email updates on Latest Tutorials

  • C Programs
  • Java Programs
  • SQL FAQ’s
  • Python Programs
  • SSIS
  • Tableau
  • JavaScript

Copyright © 2019 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy