Help in inserting a loadId...

[InfSource]

Hi,

I'm using informatica to load data from xml files to our oracle db. I have mapped all the incoming fields and it works. But one problem I'm facing is, I want to have a Load Id to be stored in my database to track relationships.

For example, I have a sample xml like following:

<Customer Id="cust001">
<Addr>
<Street></Street>
<City></City>
...
</Addr>
<Order>
<Items>
<Item>
<ItemId></ItemId>
<Price></Price>
</Item>
...
</Items
</Order>
</Customer>

I have mapped the fields to tables like Customer, Addr, Order etc. Now, for a customer record, I want to put a unique LoadId into Customer, Addr, Order tables which will show the records received together and are related to one customer.

I have tried to create an expression transformation having LoadId (created using sysDate function) and custKey(mapped Customer Id key to this field). And I want to link this LoadId to all the target table/transformation but informatica is not allowing to link from a transformation to other transformations/targets.

Is there a way I can do this? Why I can't link LoadId from on transformation to other transformations/targets ?

Any help would be greatly appreciated.
Thanks

 

[blom0344]

Let's handle the last bit first. Up untill version 7 you cannot rejoin within a mapping. So, if you have set multiple targets there is no way to rejoin the various paths. (perhaps version 8 will change this)

Secondly, try to revise your strategy. Lots of issue can be solved by NOT trying to fix everything in a single passthrough.

In your case, consider a first step that reads the xml files and writes just the custkey and the loadid to a intermediate table. Now use this in the next steps as a lookup to populate other tables with the proper loadid.

If you create a single workflow from these elements you can accomplish what you want by multiple passes through the your source data. Be sure to build in checks within the workflow though..



Ties Blom