Implementation of XML Extract in SSIS

SQL Server Integration Service (SSIS) :

SSIS is a component of the Microsoft SQL Server database software that can be used to execute a wide range of data migration tasks. SSIS is a fast & flexible data warehousing tool used for data extraction, loading and transformation like cleaning, aggregating, merging data, etc.

It makes it easy to move data from one database to another database. SSIS can extract data from a wide variety of sources like SQL Server databases, Excel files, Oracle etc. In this blog we are going to see how to implement XML Extract in SSIS.

Control Flow:

    A Control flow defines a workflow of tasks to be executed, often in a particular order. Control flow makes it possible for the package to extract, transform, and load data

Data Flow:

The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and let the user transform, clean, and modify data.

XML Extract:

The XML Extract Component is an SSIS transformation component that receives an XML document from an upstream component and extracts data from the received XML documents and produces column data for the SSIS pipeline.

Let us illustrate this with an example.

XML Extract Component of the SSIS KingswaySoft. In this the source will be the procedure from where the data will extracted in the xml format and destination will be the table.

Step 1 – First drag and drop data flow task from the SSIS Toolbox in the control flow window.

Step 2 – Next, Double Click on the Data flow task from Step 1 and in the below screenshot given drag the OLE DB Source component it will call the procedure that will generate the output in the xml format.

Step 3 – Designer page allows you to build the design of the document you are trying to extract. Now, the source will be connected to the XML Extract component which produces column data for the SSIS pipeline.

DESIGN

COLUMNS

In the above image the column page shows the available columns from the Design Page. The Columns Page grid consists of:

• Column Name: Column that will be retrieved from the document.
• Data Type: The data type of this field.

Hide Unselected Fields

• When the Hide Unselected Fields checkbox is checked unselected output columns will be hidden.

Hide Selected Fields

• When the Hide Selected Fields checkbox is checked used selected columns will be hidden.

There are a couple of special columns to take note of:

• _RowIndex: This column contains the current count of this output element.
• _ParentKeyField: This column contains the value of this records parent key field.

ERROR HANDLING

In this there are 3 options for error handling

1. Fail on Error
2. Redirect rows to error output
3. Ignore error

For this example we selected Fail on error option. If the package fails it will show the error message.

Step 4 – There are 3 output from the xml extract Order, Order Line, OverchargeTotal from which we need the specific columns from order and overchargetotal to be merged and show in one destination so, we used Merge join and Sort Transformation

Step 5 – Drag and drop the OLE DB Destination component and specify the target table in which the data needs to be loaded.

Now, The xml message is successfully loaded in the column format in the target table.

Here we learned that how to implement the XML Extract component in SSIS.

Refer to the official KingswaySoft documentation here if you want to learn more.

for more such blogs click here

Happy Reading!!