How do I export an XML file via SSIS package?
-
I have a large t-sql statement (select... for xml explicit) that creates a nice sized (17 mb) xml file that I need to ftp to a vendor on a daily basis. I can run the the select statement in management studio where I can open up, view, and save the results as an xml or text file and then manually ftp the file to the vendor.
What I am struggling with is automating this process via SSIS. Right now I have the t-sql running in an ole db source compnent. It generates the output as a DT_IMAGE data type. Now I'm stuck. I have scoured the net and have not been able to successfully apply any of the suggested solutions (bcp with a select statement from the command line, using a datareader destination, etc).
Any help/advice is much appreciated.
Dave
-
Just about to leave work so a quick reply.
Try running the select statement in a SQL task, and save the result as XML to a variable.
Then, use an XML task to write out the variable to a file. Not sure how it will deal with such a large file, but it should work in theory...
plus, you can transform it with XSL if necessary.
anyone else with other thoughts?
Tom
Life: it twists and turns like a twisty turny thing
-
Thanks for the reply Tom.
I tried your suggestion, but was unable to process it without getting errors. I wasn't sure whether I needed to do an xslt, xpath, diff, merge, etc... I tried a few but kept getting errors.
I eventually came accross the following ways that suggest running the sql statement into a variable and then writing the variable out to a file via a script:
http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/05/22/21084.aspx#27317
http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx
I used the first one and it worked well. Thanks again...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply