Home Dashboard Directory Help
Search

SSIS: XML Destination Adapter by Yitzhak Khabinsky


Status: 

Closed
 as Won't Fix Help for as Won't Fix


75
0
Sign in
to vote
Type: Suggestion
ID: 303815
Opened: 10/10/2007 8:22:12 AM
Access Restriction: Public
9
Workaround(s)
view

Description

XML is venerable today. Lots of ETL processes need to generate XML files which are replacing plain text files as a universal data format for data exchanges.
As of now, SSIS is missing XML Destination Adapter.
Details
Sign in to post a comment.
Posted by Vancalbergh Vincent on 8/13/2013 at 2:45 AM
I also feel this is something that should have been included since the introduction of SSIS. XML is handled very well inside SQL, but you wouldn't say if you look at SSIS.

It should practically be classified as a bug and introduced in older versions using a hotfix !
Posted by Mark Tassin on 2/5/2013 at 1:30 PM
I agree with katie and Emil. This should be there, some days I have to export xml... would be nice to have an easy to use standard component that I can feed an xsd to and then map fields.
Posted by Katie and Emil on 12/27/2012 at 4:47 AM
I would appreciate if you could re-open the request and re-assess if it can be put in next release.
Posted by COZYROC on 8/11/2012 at 3:32 PM
John,

You obviously didn't understand the rationals and design goals behind COZYROC's Template Task. These are the benefits of our solution:

1. The reason why the Template Task is implemented as a task is to support having multiple data flows connected to one template. With this design you can generate very complex documents. This is not possible if it was implemented as component for use in a single data flow.

2. The language John calls special is the open-source Velocity templating engine, which has been available even before the .NET framework came on the market. The implementation is very similar to designing an ASP template. There are numerous documentation sites and books written about it and it is very simple to use and understand by non-programmers. Writing C# and VB.NET is much more complicated. Also learning the Velocity engine syntax makes you better professional and these skills can be used on other projects outside SSIS. The same cannot be said about keelio's solution, which is not available outside SSIS. Since John's post, keelio developers have actually implemented their own templating syntax because apparently there are limits of what can be accomplished using point-and-click UI. This essentially validates our approach is superior to the keelio product.
Posted by Kuthuparakkal on 8/2/2011 at 2:17 AM
Goto here you have a fully functional free XML Destination compatible with InfoPath form generation

https://sites.google.com/site/kuthuparakkalcom/downloads/XmlDestination.zip?attredirects=0&d=1
Posted by JohnS_72 on 12/22/2010 at 1:11 PM
There is an even better third party solution available that allows you to create XML entirely within a data flow and do multi-level XML using merge joins within the data flow. It is available from http://www.keelio.com. The main advantage of this component is that you don't have to learn a special programming language that Cozyroc's component uses. I'm not sure why they decided to replace functionality of the script task with their custom programming language (Velocity). Also cozyroc's component is only available outside of the data flow and you have to learn all their other tools to use it effectively.

So if your looking to create XML use the plain old script task or give the template transformation component a look at http://www.keelio.com
Posted by COZYROC on 1/27/2010 at 11:13 AM
There is a third-party 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
Posted by Jens K. Suessmeyer - on 11/10/2009 at 4:19 AM
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
Posted by vikas_k on 4/1/2009 at 5:08 AM
I too thought it was there somewhere but honestly was shocked to see that this basic functionality is missing.

there are workarounds for the same but still the product is incomplete without this basic functionality. When you XML Source then why not also have an XML Destination?
Posted by Microsoft on 12/1/2008 at 10:08 AM
Reactivating for consideration in a future release. We've got a lot of potential work on our plate, so we can't promise this in any specific release. However, we do recognize it is of significant value and we do appreciate your feedback. In the meanwhile, the product team has provided a sample XML destination at http://www.codeplex.com/SQLSrvIntegrationSrv that may server your needs.

Thanks,
- Matt Carroll
Posted by Carlos.V on 11/3/2008 at 2:33 PM
I'm very disappointed to see this didn't make it in SSIS 2008 either. This is a basic functionality that should have been included with SSIS from the very beginning.
It's clear that the reason why this didn't make it into the product is not one of complexity but of Microsoft's self interests. They want to make it very easy to import data into SQL server(and other MS products. e.g. Excel, ADO) , BUT not so easy to export it to an open and portable standard as XML.
Posted by vistathis on 11/3/2008 at 6:56 AM
I am absolutely shocked that this isn't included in SSIS, but I guess I'm not that shocked that the consumers priorities don't match Microsofts. {Closes SSIS studio indefinately}
Posted by xyvyx on 9/11/2008 at 12:26 PM
wow...

hadn't tried to create XML files directly until now... seems easy to generate an XML result set in SQL, but did nobody think that we might want to create XML files from it? SSMS is borked since it throws in a CR/LF every 2033 bytes.

Posted by Matt Olson on 7/2/2008 at 12:55 AM
There is an XML destination component for SQL 2005 (and SQL 2008 when it's released) from Keelio Software. You can get it at http://www keelio.com.
Posted by OhioHouser on 3/27/2008 at 6:55 AM
Not a top priority? With XML being one of the most used transport mediums in use today, what would be a higher priority for an ETL tool? Especially when Microsoft is touting it as supporting XML. Amazing.
Posted by Pierre Voinson on 1/15/2008 at 7:25 AM
How do I get the "XMLDestinationAdapter.zip" file attachement from this website?
Posted by Sam Bendayan on 10/16/2007 at 1:04 PM
It sounds very strange to me that this is not a top priority. I actually remember looking for this adapter when I first started using SSIS and was surprised that it was not there. It's one of those obvious things that you totally expect to be there, even in the first version of a product....and now to find out that it won't even be in 2008 is nothing short of amazing. SQL Server has other areas of XML support that have been there for a while and were top priority back when they were introduced (and were also heavily marketed)....why would this be any different?
Posted by Edson Crevecoeur on 10/16/2007 at 12:11 PM
I agree with ykhabins. An XML File destination is essential for cross-platform data exchanges.
Posted by Yitzhak Khabinsky on 10/16/2007 at 11:30 AM
IMHO, it is a poor decision.

Being on the TAP program, I asked for the XML Destination Adapter back to MS SQL Server 2005 version (Kirk Haselden era, circa 2004). If it won’t make into MS SQL Server 2008 it means “may be” in MS SQL Server 2011?! 7 long years to wait for a relatively simple adapter.

I hope your decision is not final. Please reconsider and reopen this feedback for implementation.
Posted by Microsoft on 10/16/2007 at 10:52 AM
Thank you very much for your feedbacks. However, this is not into our current priorities. We will look into it in the future. Please continue supporting our product and providing us your value feedbacks,

Posted by Yitzhak Khabinsky on 10/10/2007 at 1:17 PM
Credit also goes to John Welch for generating the original idea.
http://agilebi.com/CS/blogs/jwelch/

Regards,
Yitzhak

Sign in to post a workaround.
Posted by Vancalbergh Vincent on 8/13/2013 at 5:22 AM
Already posted in the comments:
http://ssisctc.codeplex.com/wikipage?title=XmlDestination&referringTitle=Home
Posted by kristl tyler on 5/21/2012 at 1:23 PM
Fast, easy, elegant work around. Uses existing toolset.

http://businessincredulous.blogspot.com/2012/05/ssis-xml-output.html
Posted by SSISJoost on 1/21/2011 at 6:48 AM
An other C# Scripttask work around:
http://microsoft-ssis.blogspot.com/2010/12/flexible-xml-destination.html
Posted by TheUnforgiven2 on 1/11/2010 at 5:49 AM
There is a good workaround at:
http://sqlblogcasts.com/blogs/simons/archive/2009/06/23/Exporting-XML-data-from-SSIS---nugget.aspx
Posted by Jens K. Suessmeyer - on 11/10/2009 at 4:20 AM
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
Posted by MSeidler on 9/9/2008 at 5:10 AM
One easy way to create an XML file in .Net is to use the WriteXml() method of a dataset. Unfortunately the DataReaderDestination dataflow component does not support this.
After a little research I found an official SSIS (2005) sample called DatasetDestination. With this sample you can create a custom dataflow component representing a dataset with the interesting property "SaveDataSetToXml". If this is set to true, the output is saved to a file connection.
For further information see the MSDN documentation for the sample.
Posted by Rye.Guy on 7/14/2008 at 10:59 AM
Probably depends on how large the .xml file is, but this solution worked fine for me:

I used FOR XML AUTO, ROOT,ELEMENTS XSINIL for my query then converted that XML result into varchar(MAX) within SQL 2005.

Then in SSIS I used a dataflow task with an OLE DB SOURCE to run the query and outputted the result into a Flat File Destination. The flat file was Delimited with Header {CR}{LF}.
Posted by Matt Olson on 7/2/2008 at 12:56 AM
There is an XML destination adapter available at http://www.keelio.com
Posted by Mark Guinness on 4/10/2008 at 12:27 PM
I've used a script destination to create an XML file:

Dim writer As XmlTextWriter

Public Overrides Sub PreExecute()
    writer = New XmlTextWriter("data.xml", Nothing)
    writer.Formatting = Formatting.Indented
    writer.WriteStartDocument()
    writer.WriteStartElement("Rows")
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    writer.WriteStartElement("Row")

    For Each prop As PropertyInfo In rowType.GetProperties()
        If Not prop.Name.EndsWith("IsNull") Then
            If CBool(rowType.GetProperty(prop.Name & _
                "_IsNull").GetValue(Row, Nothing)) = False Then
                writer.WriteStartElement(prop.Name)
                writer.WriteValue(prop.GetValue(Row, Nothing))
                writer.WriteEndElement()
            End If
        End If
    Next

    writer.WriteEndElement()
End Sub

Public Overrides Sub PostExecute()
    writer.WriteEndElement()
    writer.WriteEndDocument()
    writer.Close()
End Sub
File Name Submitted By Submitted On File Size  
XMLDestinationAdapter.zip (restricted) 10/10/2007 -