XML Destination

Jul 16, 2009 at 9:05 PM

Hi All,

      I am trying to create an xml destination file using the xmldestination component. Can some one help with some SSIS sample.



Jul 20, 2009 at 7:28 AM
Edited Jul 21, 2009 at 7:26 AM

I think we've found the best solution, well so far. We found that using 3rd party components just aren't complete enough to handle all the different XML requirements.

1) The solution is to push the data into a table, preferably a normalized table structure in the XML structure {!format} you want out. {Corrected}

2) Then useing OPTION EXPLICIT in your SELECT statement build your desire3d output format. This is a mission to get your head around, but once you got it, it's a dream to use.  "http://msdn.microsoft.com/en-us/library/aa226532%28SQL.80%29.aspx"

3) stream the variable to a flat file using a script task...

Have a go.


Jul 20, 2009 at 2:24 PM

Thanks Nisus but when I tried to push the data in xml format to the table I am receiving the following error.

Msg 6819, Level 16, State 1, Line 86<font size="1">

The FOR XML clause is not allowed in a INSERT statement.



Jul 21, 2009 at 8:16 AM

Hello Baskaran, I tried to be too brief in my previous post. I'm going to elaborate a little more here. Can you post an example piece of XML data that you are using?

1) The first step is to get the data into a SQL table. At first, to get going, just keep it 1 flat denormalized SQL table of data.

2) Using Management Studio, build you XML query using OPTION EXPLICIT and get the XML well formatted as you would eventually like to see it in an XML file. The above MSDN web address will explain in full detail how to use OPTION EXPLICIT to get what you want. Once you have the quary ready to go. Place this into an "Execute SQL Task". In "General", set the "ResultSet" property to "XML". In "Result Set" tab, set "Result Name" = 0 and the variable name to a string variable, which you can create at this point, for example I've created one called "PersonXML".

3) Then using a script task and using the variable as a ReadOnly variable. Here is the example of how we wrote the XML data out to a flat file. The connection was previously created as "agm_person.xml".

String XMLString = null;
String XMLFileName = Dts.Connections["agm_person.xml"].AcquireConnection(Dts.Transaction).ToString();
StreamWriter fs = new StreamWriter(XMLFileName, false);

XMLString = Dts.Variables["PersonXML"].Value.ToString().Replace("<ROOT>", "").Replace("</ROOT>", "");



Hope this helps a little more. I a little busy today to give the complete details.