SQL Server 2005 SSIS Can’t export XML !?!?

SQL Server 2005 is singing and dancing about it’s new XML features.

Well, I have a tiny table in SQL Server which is 81rows with 7 columns. I want it to be queried on our public web site and so was planning to use SSIS to export it to XML and FTP it. I thought this was gonna be a 5min job, but alas not…

There is no XML Destination in Data Flow Destinations, having done some googling I concluded that this is the case and I’m not missing anything, so back to the drawing board…

I found an article which said ‘just’ do the XML in SQL and export that, so we set about generating some nasty,nasty,nasty (Mark’s input there) XML in SQL Server using something like:
SELECT Col1,Col2,Col3 FROM myTable WITH XML AUTO

At this point I have something XML like, fine, now all I have to do is export it to a file. Again scuppered as as far as I can tell I only have two possible options in the Data Flow Task: “Flat File Destination” (CSV) or “Raw File Destination”. Neither work as the XML ‘column’ kicked out by the SELECT query is of type DT_IMAGE.

I’ve read a suggestion at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65534
Which suggests DIYing it, I think that’s a last resort…

UPDATED: Actually it’s possible using powershell, checkout http://www.pluralsight.com/blogs/dan/archive/2006/10/28/41337.aspx
Failing that you can quite easily modify the powershell script included in my DiagramScripting post

Advertisements

7 responses to “SQL Server 2005 SSIS Can’t export XML !?!?

  1. I came across your article online about ‘SSIS can not export XML’ when I was searching for a solution for weeks. I found out the same thing and wondering what to do for my project. I don’t know about .net programming a lot but might need a solution when we migrate from SQL Server 2000 to 2005 later. Considering I have written a ActiveX script for a DTS package to do the job (not tested yet), have you found a solution (not programming) in SSIS for this problem?

  2. No, in the end we wrote something. There doesn’t appear to be an SSIS solution.

    If I had to do it again, I’d probably look at some of the old Stored Procedure functionality for gaining access to the command shell to see if I could pipe it out that way. Failing that possibly a linked server?

    UPDATED: Actually it’s possible using powershell, checkout http://www.pluralsight.com/blogs/dan/archive/2006/10/28/41337.aspx
    Failing that you can quite easily modify the powershell script included in my DiagramScripting post

  3. There is an XML Destination adapter available at http://www.keelio.com to solve this limitation.

  4. I converted my XML result set in SQL to varchar(max)so that it came into SSIS as a DT_TEXT. Then it was simple to output it to a Flat File Destination.

  5. Ryan – I would like to do this as well. Is there a size limit to this option though?

  6. There is a good workaround at http://sqlblogcasts.com/blogs/simons/archive/2009/06/23/Exporting-XML-data-from-SSIS—nugget.aspx

    I generate the XML data in the structure I need (quite complex), and use this to export the data to a file. Works well, except that you can’t use connection managers to control the file location.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s