Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

Validate XML File using SSIS XML Task

by suresh

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.

Validate XML File using SSIS XML Task 1

The following screenshot shows the XSD file that we are going to use in this example

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

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

Validate XML File using SSIS XML Task 4

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

Validate XML File using SSIS XML Task 5

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.

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

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.

Validate XML File using SSIS XML Task 8

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

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

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

Validate XML File using SSIS XML Task 15

Now, let us rerun the SSIS Xml task validate 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 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.
Validate XML File using SSIS XML Task 18

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

Validate XML File using SSIS XML Task 19

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

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

Home | About Us | Contact Us | Privacy Policy