Re: SSIS: ET flat text file to multi-level hierachal XML.

Hello Benjamin,

I don't know whether MS intend to release an XML destination adapter. As for your solution.

Why did you need to drop into temp tables?

You are right though in that it may be easier for you to ask the feed providers to drop to you multiple csv files, load them into the pipeline, use a merge join perhaps then eventually use a Script component as a destination and craft the XML yourself.


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

SSIS seems to lack a powerful feature that Informatica PowerCenter and
GXS AI software provides. That is the ability to extract and group
data in a semi-structured flat text file to XML with multi-level
hierachal XML intutively without hand coding.

With SSIS, I had to extract the formatted report flat text files in to
SQL Server temp tables, then use lookups and OLE DB SQL commands to
update the foreign key relationships across temp tables. Then
manipulate the temp tables and load them to the working tables.

That is a lot of work, and the performance is bad for this type of
work.

Using other products such PowerCenter, it can transform flat text file
into XML. Then pass the XML to a stored procedure for additional
extraction and process into multiple tables. The performance wise
seems better.

One solution now is to have the provider of flat text file provide us
the unformatted report file in multiple grouping in CSV file, or
provide us XML files so we can easily do the ETL part.

Has anyone experience the same issues I'm having?

Will SSIS have XML Destination capability like PowerCenter in the
future?
Thanks,
Benjamin


.

Relevant Pages

  • Re: XML Index on xml variable?
    ... The hypothetical performance difference between using a temp table and a ... slightly higher cost for logging and locking), ... of creating an XML index is much higher than the cost of inserting your XML ...
    (microsoft.public.sqlserver.xml)
  • Re: SSIS: ET flat text file to multi-level hierachal XML.
    ... report flat file. ... header, multiple types of details having different detail columns, and some ... So, for now it was extracting all sections, then I dump them to temp tables. ... Then we probably won't even need XML. ...
    (microsoft.public.sqlserver.dts)
  • Re: Convert String data from Web Service to XML
    ... Are you using Xlang or external .net assembly to load xml? ... XmlDocument temp2 = ... why not use temp instead of temp2, ... I am trying to Convert String data from Web Service to XML ...
    (microsoft.public.biztalk.general)
  • Re: export to XML file - poor performance
    ... I'm trying to create PL/SQL procedure which will export data to xml ... Why do you think the code snip you posted relates to the TEMP ...
    (comp.databases.oracle.misc)
  • Re: XML Index on xml variable?
    ... Creating an XML Index on XML variables is not supported. ... The hypothetical performance difference between using a temp table and a ... DECLARE @MyVar xml ...
    (microsoft.public.sqlserver.xml)