How to export SQL Data to XML via SSIS
Hi, Is there a sample or anything that shows what the best way of exporting SQL data to XML?Thanks,David
June 13th, 2005 3:57pm

This topic in Books Online is helpful.ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2b6b5c61-c5bd-49d2-8c0c-b7cf15857906.htmYou can use the SQL Task to query for XML like this. USE AdventureWorksGOSELECT * FROM Person.Contact FOR XML AUTOGOI don't know if there is a "best" way. It really depends on your requirements; what will work best for you.Thanks,
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2005 6:38pm

The pipeline may be a good fit here in a destination adapter. Whilst you use the trigraph S.Q.L. this could easily fit Foxpro, Excel, Oracle etc and they may not be as kind to XML as SQL Server (current releases).I would be interested in how you would expect this to be handled for you. I can think of a couple of ways but am not sure if they match your way.Allan
June 14th, 2005 1:22am

How about if this data is not coming from SQL. I mean if this data is processed in the Data Flow Task and then we want to publish it to an XML file. Because there is no XML Destination.??
Free Windows Admin Tool Kit Click here and download it now
June 16th, 2005 2:12am

It is really a lack in SSIS. Export into XML from source other than SQLServer is not available. You have to develop your own SSIS components or try to find some on the Net...
November 9th, 2007 2:13pm

You can roll your own in a script component pretty easily. There's a sample here (among the many others out there): http://agilebi.com/cs/blogs/jwelch/archive/2007/06/01/xml-destination-script-component.aspx
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2007 7:42am

Philippe Bonneau wrote: It is really a lack in SSIS. Export into XML from source other than SQLServer is not available. You have to develop your own SSIS components or try to find some on the Net... But answer me this. What would those XML files look like? What would the structure of them be? Would you like to be able to define that structure?Would you want values to appear as node attributes or as elements? Once you have answered those questions then go ahead and submit a request to https://connect.microsoft.com/SQLServer/feedback/ if you want to solicit change then that is the place to go. And please repost a link up here when you are done. -Jamie
November 11th, 2007 5:42pm

Such request is already there. It is called SSIS: XML Destination Adapter Here is the URL: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=303815&wa=wsignin1.0 Regards, Yitzhak
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2007 8:12pm

There is an XML destination adapter available at http://www.keelio.com
July 2nd, 2008 10:57am

A maybe simple way to achieve this can be found here: http://blogs.msdn.com/jenss/archive/2009/11/06/an-secret-ssis-xml-destination-provider-you-might-not-found-yet.aspx(Your implemenations may vary)-JensJens K. Suessmeyer http://blogs.msdn.com/Jenss
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2009 3:19pm

A maybe simple way to achieve this can be found here: http://blogs.msdn.com/jenss/archive/2009/11/06/an-secret-ssis-xml-destination-provider-you-might-not-found-yet.aspx (Your implemenations may vary) -Jens Jens K. Suessmeyer http://blogs.msdn.com/Jenss And maybe you could use Jens' technique in conjunction with Josh Twist's XMLify: http://www.thejoyofcode.com/SSIS_Xmlify_Data_Flow_Task.aspx#commentbbb518c1-3d9f-4b1d-bfca-c994f42678aa -Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
November 10th, 2009 3:37pm

One easy way to send SQL Data as XML is to extract sql data as xml and sending it out as text or xml using file system connection. I am not sure you really need a XML Destination control.You may want to read this article which explains the procedure:http://hodentek.blogspot.com/2009/11/do-you-need-to-send-data-in-xml-use-sql.htmlmysorian
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2009 1:02am

Wile the solution mentioned by you needs an extra script tasks, the one I mentioned uses a flat file listerner which would be a more native way to get things done in SSIS, even that you have all the property for configuration like replace if existing etc.-JensJens K. Suessmeyer http://blogs.msdn.com/Jenss
November 30th, 2009 11:36am

There is a third-party commercial solution available, which can generate arbitrary type of text documents. It is called CozyRoc Template Task. The setup is similar to the creation of ASP or JSP page where you create text template and then you can connect variables, files and data flows as inputs. You can find more information about it here: http://www.cozyroc.com/ssis/template-task SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
January 27th, 2010 10:19pm

not able to see the page.. it says the page not found error.. can you please provide me the code to write the xml destination script component
September 15th, 2010 9:50pm

You should also look into using a script to generate an xml file. I have a couple of SSIS packages that create xml files using the system.xml assembly in .Net 2.0 (i.e. the XML classes available in SQL Server 2005). Take a look at the XMLWriter class and any script examples in VB that use it. You can do the same processing in SSIS script tasks and components.
Free Windows Admin Tool Kit Click here and download it now
September 15th, 2010 11:30pm

Here is a SSIS 2008 C# example of a script component that uses .Net reflexion to create a xml output for all columns. http://microsoft-ssis.blogspot.com/2010/12/flexible-xml-destination.html
December 27th, 2010 3:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics