An “secret” SSIS XML Destination Provider you might not found yet
(Sample code included at the end of the post)
The initiator for this post was Dan Atkins who wanted to create a feed from relational data to consume it directly from a created gadget.
Where can I find that in the toolbox ?
First of all, you won’t a XML destination adapter as of the shipped components in SQL Server 2005 and 2008. There are for sure third party components which can directly convert data from the data pipeline to defined XML but sometimes it is much easier than that and you just want to create an XML file from any data source which is able to produce XML look-a-like data. What does that mean ? Let me show you in a quick sample.
Many people are not aware of the great XML handling relational databases like SQL Server are capable of. They can generate XML data from a relational set / query and give you the string representation or the binary data to work with.
The older brother of XML
So the destination should be a XML file, right ? How would you describe a XML file in comparison to any other file type like a Word-Document ? Well, compared to a Word document, XML can be opened and read in plain text with any reader like notepad. At the end it is simply a flat file with clear text data. The older brother of XML files is a CSV file which can be produced by SSIS using a Flat File destination. Not touching the logic of XML files, it can be compared to a CSV with one column of data (That is really a high-level view :-)
But that is the direction this sample will talk about. We want to get data from a source that can produce XML Data representations (which can also be script tasks) and create an XML file from that. (See my former blog post on that here)
Creating the sample SSIS package
The source
For that I create a SSIS package with a simple OLEDB source.
As I wanted to make it easily reproducible for you without having the need to create a Northwind or Adventureworks database on your machine, I used the new feature of row constructors in SQL Server 2008 which is able to create a table on the fly within a query (very handy if you don’t want to persist static data which is only used for one single purpose). Notice that I created a full XML set with a root and several nodes. If you execute this in the execution engine of your choice, you will already get a nice XML representation. Depending on your needs, you might want to put some data in attributes instead of nodes, but that is all described in he blog entry below.
Notice that I put a SELECT ( XMLQueryHere) AS YourColumn in the query, as this will directly bring back the text representation of the XML to the output. Without that you will get binary data (System.Byte[]) which might not be the right choice in that situation. I addition, the created column names will have GUIDs within if you do not use this notation making it hard to have a predictable column name for the mapping later on.
The target
The target is even simpler than the source. Map the output of the source to the flat file destination and open the editor of the flat file destination. It is a flat file destination (created as a UNICODE file) create manually a column of type DT_NTEXT (1). Deselect the Option “Column headers in the first row” to get the pure value of the XML. Navigate to the Flat file destination adapter and map it in the Mappings section the XML input column to the destination flat file column and you are already done.
The result
Running that will bring you the pure XML created by the relational engine (in t´hat case SQL Server). I a aware that this isn’t the 100% perfect pipeline version of the XML adapter, but sometimes this is already enough to make data interchangeable with other partners and prevent you from using bcp and dynamic SQL execution at all.
The sample SSIS package can be downloaded here.
