Validate XML File using SSIS XML Task

How to Validate XML File using SSIS XML Task with example? Before we get into the example, let us see our data 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 the SSIS XML task.

XML File 1

The following screenshot shows the XSD file we will use in this example.

XML File 2

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

Validate XML File using SSIS XML Task 3

Double-click on it will open the XML Task Editor to configure it. Please refer to the below examples to transfer XML files and find the differences.

In this SSIS example, We are validating the XML file, so please change the operationType property to Validate

Validate File using XML Task 4

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.
  • If you want to input the XML data directly, select the SourceType property as Direct Input.

In this example, we use the above-specified books.xml file as the source. So, we are selecting the SourceType property as File Connection. If you have 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..>.

Validate XML File using SSIS XML Task 5

Once you click 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 the Browse button to select the Existing File from our file system.

Validate XML File using SSIS XML Task 6

From the below image, see that we selected the books.xml file inside the Destination Folder

Validate XML File using XML Task 7

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.
Validate XML File using SSIS XML Task 11

We must configure the Second Operand Type (please select the validation file xsd or dtd). So, Please select the SecondOperandType property.

  • If you use the XSD file present in the local file system, 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 a validation file. So, we are selecting the SecondOperandType property as a File Connection.

Validate XML File using SSIS XML Task 8

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

Validate XML File using SSIS XML Task 9

As you can see from the below screenshot, We selected the books.xsd file inside the Destination Folder.

Validate XML File using XML Task 10

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.

Validate XML File using SSIS XML Task 12

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.

Validate XML File using SSIS XML Task 14

The above screenshot shows that our SSIS XML task validates the 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

Validate XML File using SSIS XML Task 15

Let us rerun the SSIS XML task to validate the XML file package. From the below screenshot, you can observe that our package failed.

Validate XML File using SSIS XML Task 16

Let’s open the Progress tab to see the problems.

Validate XML File using SSIS XML Task 17

TIP: In real-time, we store the output result in a variable, and then we may email the information to the 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 their line number and position; otherwise, it will not include its details.
Validate XML File using SSIS XML Task 18

Let us open and run the Validate XML File using the SSIS XML Task package and see the result by opening the yes.txt file.

Validate XML File using SSIS XML Task 19