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. The below screenshot shows the data inside that books.xml file. Our job is to validate this XML file using SSIS XML task.
The following screenshot shows the XSD file that we are going to use in this example
Validate XML File using SSIS XML Task
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 SSIS example, We are validating the XML file, so please change the operationType property to Validate
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 input the XML data directly, 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 open to configure it. In this example, we are validating the existing XML file. So, we are selecting 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 image, see 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, the XML task uses the XML Schema Definition file as the validation file. In this example, we are using this option
- DTD: If you select this option, the SSIS XML task uses 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 input the XSD data directly, 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 a 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..>.
As you can see from the below screenshot, 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, the XML Task will fail the task.
Click OK to finish configuring the Validate XML File using the SSIS XML Task package. Let’s run and see whether our books.xml file is valid or not.
From the above screenshot, you can see that our SSIS Xml task validate XML file package runs 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 SSIS Xml task validate XML file 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, let us store the output in a Text file present in our local file system. 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, the 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, 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 Validate XML File using SSIS XML Task package and see the result by opening the yes.txt file
