The SSIS XML Task allows us to perform Six types of Operations on XML files. In this article We will show you, How to Validate XML File using SSIS XML Task 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. Our task is to validate this XML file.
Following screenshot will show you the XSD file that we are going to use in this example
Validate XML File using SSIS XML Task
In order to validate the XML File, First Drag and drop the XML Task into the Control Flow region and rename it as Validate XML File using SSIS XML Task
Double click on it will open the XML Task Editor to configure it.
In this example, We are validating the XML file so, Please change the operationType property to Validate as shown in the below screenshot
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.
- 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 validating existing 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
ValidationType: This property is used to validate the given XML file. It has two options:
- XSD: If you select this option then XML task will use the XML Schema Definition file as the validation file. In this example, we are using this option
- DTD: If you select this option then XML task will use the Document Type Definition file as the validation file.
Now we have to configure the Second Operand Type (please select the validation file xsd or dtd) so, Please select the SecondOperandType property.
- If you are using the XSD file present in the local file system then, Please select the SecondOperandType as File Connection.
- If you stored the XSD file in the Variable then, Please select the SecondOperandType as Variable and select the variable name.
- If you want to directly input the XSD data then, Please select the SecondOperandType property as Direct Input.
In this example, we are using the above specified books.xsd file as validation 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..>.
From the below screenshot you can observe that, We selected the books.xsd file inside the Destination Folder
FailOnValidationFail: This property has two options: True and False. If we set this property to TRUE, if there are any validation problems then XML Task will fail the task.
Click OK to finish configuring the Validate XML File using SSIS XML Task package. Let’s run and see whether our books.xml file is valid or not.
From the above screenshot you can observe that, our package run successfully.
Now, let us change the books.xml file. From the below you can see, we added <republished_date> node for book id bk001
Now, let us rerun the package. From the below screenshot you can observe that, Our package failed.
Let’s open the Progress tab to see the problems.
TIP: In real-time we store the output result in a variable and then we may email the information to Package developer.
For instance, In this example, let us store the output in a Text file present in our local file system. In order to do so first, we set the SaveOperationResult property to TRUE, and configure the destination. From the below screenshot you can observe that, we are selecting the yes.txt file.
- OverwriteDetination: This property has two options: True and False. If we set this property to true then XML Task will overwrite the existing files in the Destination path.
- ValidationDetails: This property has two options: True and False. If we set this property to true then, the output file contains detailed information about the errors including its line number and position otherwise, it will not include its details.
Let us open run the package and see the result by opening the yes.txt file
Thank You for Visiting Our Blog.
