SSIS XML Source With Random Child ID??

Hi,

I am trying to load a XML document using SSIS into relational tables and pulling my hair out.

In it's simpliest form I have:

<Batch>

<POSBasket>

<Header>

<SomeDetail/>

</Header>

</POSBasket>

<POSBasket>

<Header>

<SomeDetail/>

</Header>

</POSBasket>

</Batch>



Now, when you have a parent child situation, you get a column in the output called xxx_Id, in my case, POSBasket_Id. This allows you to relate your header back to your root and SomeDetail back to header etc etc.

Loading all of this in parrallel does not work because there is nothing unique once the load has happened more than once. i.e. You will have repeating POSBasket_Id's as well as Header_Id's.

I ended up loading POSBasket into a table with an identity column. Once done, I would load the header details and do a lookup (eek) on the POSBasket table by using POSbasket_Id "where isComplete = 0" (A flag to tell me current import).

Great, this works find with 1 item in my document. Add a second (2 POSBaskets) and it randomly works. (???) Add more and it hardley works. The look ups are failing with "no match found".



On using Data Views, I see that each XML source creates different POSBasket_Id's. Some matching, some not. Also seem to randomly match.

My question is why? Why would there be the ID if they differ when loading them from the same document? They not even unique across sources.



Bug or "by design"?

Anyone got a better method for doing this?



Cheers,

Crispin