Thursday, March 29, 2012

Combining XML Files

I have to combine two seperate fixed-length flat files into 1 xml file, however, the catch is the 1st flat file (header) can/will be converted to an xml file but if there is another flat file *detail) found, then we must transform that to xml and combine into the header into one xml file.

So, the standalone header xml file would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 1 info....</field2>
</row>
</rows>
</header>
</tables>
<document>

and if a detail file was found, then the xml would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 2 info....</field2>
</row>
</rows>
</header>
<detail>
<rows>
<row>
<detfield1>details file 1 info....</detfield1>
<detfield2>details file 2 info....</detfield2>
</row>
</rows>
</detail>
</tables>
<document>

Seeing how you can't easily perform any if logic in a data-flow, what would be the best way to achieve this?

JAson

Are you saying there will be more than one details file? In your example, is detfield2 really supposed to contain a value from the second detail file?

|||

sorry, I meant that there would be more than 1 filed in the details file. So,

<detfield1>details file field_1 info....</detfield1>
<detfield2>details file field_2 info....</detfield2>

The tricky part is just to figure out how to manipulate an xml file (like removing just a few end tags, then inserting another xml portion, and replace the end tags that were removed) after it has been created and written to to a destination.

|||Given that there is no "XML Destination" adapter provided with SSIS, I think you're going to have to write one yourself if your goal is to end up with an XML document. If I were in your shoes, I think I'd write a custom, managed (not script) component. I'd give it two inputs: One for the header row, and the other for the detail rows. There's a good sample in BOL that you can use to get started.

No comments:

Post a Comment