Hadi Fadlallah
This image shows how XML value was sent in one row to the flat file destination that acts as SSIS XML Destination

SSIS XML Destination

October 11, 2019 by

Until now, Microsoft has not included the XML Destination component in SQL Server Integration Services (SSIS). Many years ago, this component was requested on the Microsoft connect website, but it was closed as “Won’t fix.” For this reason, many workarounds and third-party components were created. In this article, we’ll talk about these components and some of the popular solutions for exporting data to XML using SSIS.

SSIS XML Destination sample

Our first solution is a custom SSIS component created by the Microsoft SSIS team. It started in the SSIS Community Samples project on Codeplex, and later migrated to GitHub.

As described in the readme file, it is a complex data flow task Destination component created using C#, and it includes features such as:

  • The Destination can be used with multiple inputs and merges the data from each into one Destination XML file
  • The file is created using a File Connection Manager
  • It has a custom user interface

One main issue is that this component’s latest release is relevant to SQL Server 2012. If you are using a newer version of SQL Server, you might have to edit the code and replace the old SQL Server assemblies referenced by the newer version.

Commercial SSIS XML Destination components

There are many third-party XML Destination components created by companies in this domain, such as:

KingswaySoft SSIS XML Destination

KingswaySoft provides a commercial XML Destination component that can be used to merge input data based on the XML data structure defined in the component. An XML document can be generated as a local file or can be sent to an HTTP URL to perform an XML or SOAP-based service call. For more information, refer to SSIS XML Destination

Keelio XML SSIS Toolkit

Keelio provides an XML Toolkit for SSIS that contains a component called Template Transformation, which can be used to generate XML documents. For more information, refer to XML SSIS Toolkit

ZappySys XML Destination

ZappySys provides an SSIS XML Destination component that can be used to export XML files from different data sources such as SQL Server, Oracle, MySQL, and others. For more information, refer to: SSIS XML Destination (Create XML File)

Workarounds

Here, we’ll describe two workarounds that can be used to generate XML files without the need for third-party components. The first can be used only with SQL Server source, while the second can be used with any data source.

SQL FOR XML clause

One of the simplest solutions for generating an XML File without the need for an SSIS XML Destination is to read data as XML from the data source and write it to a flat file. In SQL Server, one of the most popular solutions to read data as XML is by using a FOR XML clause within the SELECT statement. As an example, in the AdventureWorks2017 database, we tried the following query:

The result is shown in the image below:

This image shows the result of an SQL query using For XML auto

Figure 1 – FOR XML Auto example

If you click on the XML value, you can see the whole command result written as XML.

To implement that in SSIS, first add an OLE DB connection manager to define a connection with the SQL Server instance. Next, add a flat file connection manager, uncheck the Columns names in first data row checkbox, then go to the Advanced Tab, add one column and change its data type to Unicode text stream DT_NTEXT and make sure that the file name specified has an .xml extension instead of .txt or .csv:

This image shows how we configured Flat File connection manager to act as SSIS XML Destination

Figure 2 – Flat file connection manager (General Tab)

This image shows how we configured Flat File columns to act as SSIS XML Destination

Figure 3 – Flat file Connection Manager (Advanced Tab)

After creating connection managers, add a data flow task where you add an OLE DB source and a flat file destination. In the OLE DB source, change the data access mode to SQL Command. Then, if you try to use the query mentioned above, you will get a set of rows containing the System.Byte[] value shown below, since there is a problem reading the XML data type from SQL Server:

This image shows how OLE DB Source read a SELECT command using FOR XML clause

Figure 4 – Problem reading XML using OLE DB Source SQL Command

To fix this problem, you have to read the whole result as a single text value. You can do that by adding another SELECT clause and by assigning an alias to the whole query as follows:

Now, if you click the preview button, you can see the XML value as shown below:

This image shows how the problem of reading XML value from SQL Server was solved

Figure 5 – Previewing XML value in OLE DB Source

Now, open the flat file destination and map the input column to the column defined in the flat file connection manager.

When you execute the package, you can see that only one row is transferred:

This image shows how XML value was sent in one row to the flat file destination that acts as SSIS XML Destination

Figure 6 – Only one row is imported

On the other hand, when you open the XML file, you can see that the whole result is exported.

For more information on FOR XML clause in SQL Server, refer to this article: FOR XML PATH clause in SQL Server

Script component destination

The Second SSIS XML Destination workaround can be done using two steps:

  1. Send data to a RecordSet destination
  2. Use a script task to convert the RecordSet into a DataTable object
  3. Use the DataTable.WriteXML() function to export the XML file

Here’s a detailed example of this solution:

First, create a variable of type System.Object i.e., @[User::ResultTable].

Next, add an OLE DB source that reads data from the [Person].[Person] table found in the AdventureWorks2017 database. Then, add a Recordset Destination and store the result within the @[User::ResultTable] variable.

This image shows the Recordset destination description from SSIS toolbox

Figure 7 – RecordSet Destination definition from SSIS Toolbox

As shown above, the Recordset Destination converts the fill the data into an ADO Recordset that can be consumed using a script task:

This image shows a screenshot of the  package data flow task

Figure 8 – Data Flow Task overview

Then, outside the data flow task, add a script task and select @[User::ResultTable] as ReadOnly variable:

This image shows a screenshot of the packge control flow

Figure 9 – Control flow overview

This image shows how we configured the Script Task to act as SSIS XML Destination

Figure 10 – Script Task configuration

In the Script Editor, use an OledbDataAdapter class first in order to fill the ADO Recordset stored in the variable within a DataTable object. Then use the DataTable.WriteXML() function to write the DataTable into an XML file. You can use similar code:

Run the package and check that the resulting XML file is created successfully.

To learn more about DataTable and handling ADO RecordSet, refer to:

Limitations

One main limitation for both workarounds is they may not be able to handle a huge volume of data, since both store data in memory before exporting to the XML file.

References and external links

Hadi Fadlallah

Hadi Fadlallah

Hadi is a Lebanese Researcher, Data Engineer and Business Intelligence Developer.

He has been working with SQL Server for more than 10 years. Also, he's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com

Hadi really enjoys learning new things everyday and sharing his knowledge.

View all posts by Hadi Fadlallah
Hadi Fadlallah
493 Views