Exporting XML data from SSIS - nugget
James Rowland-Jones asked me this evening how to export
XML data from SSIS. I've done this before but always used the Script component
and I wondered if there was another way.
I tried to think of a component that saves data to a file. Oddly there isn't
a control flow task that does that. The nearest I came to was the "export
column" transform, this is a hardly known transform. It was a very specific
function that most people don't need. For each row of data going through it, it
exports the contents of a column to a file with a filename specified in another
column.
Thinking of this I generated an example query that returns a rowset with a
filename column and our XML column (data).
select
'c:\test.xml' filename
, (select name, (select name
from sys.columns c where
c.object_id
= o.object_id
for xml
path('column'),type) columns
from sys.objects
o
for xml path('object'), root('schema'), type)
data
This can then be plugged into an OLEDB source, connected
to an Export Column transform configure the data column, filename and whether to
append or truncate and away you go. You can get the video here http://media.sqlknowhow.com/nuggets/Exporting%20XML%20data%20from%20SSIS.wmv
or watch it below.
-