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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT TOP (1000) [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2017].[Person].[Person] FOR XML AUTO |
The result is shown in the image below:
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:
Figure 2 – Flat file connection manager (General Tab)
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT (SELECT TOP (1000) [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2017].[Person].[Person] FOR XML AUTO) as Person |
Now, if you click the preview button, you can see the XML value as shown below:
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:
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:
- Send data to a RecordSet destination
- Use a script task to convert the RecordSet into a DataTable object
- 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.
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:
Figure 8 – Data Flow Task overview
Then, outside the data flow task, add a script task and select @[User::ResultTable] as ReadOnly variable:
Figure 9 – Control flow overview
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
public void Main() { DataTable dt; dt = (new DataSet("ResultDataSet")).Tables.Add("ResultTable"); (new OleDbDataAdapter()).Fill(dt, Dts.Variables["User::ResultTable"].Value); dt.WriteXml(@"D:\Result.xml"); Dts.TaskResult = (int)ScriptResults.Success; } |
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
- A “secret” SSIS XML Destination Provider you might not found yet
- Yet another SSIS XML Destination
- How to export data from database tables to an XML file using SSIS
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023