I have an orchestration job where each record contains, for example: Customer, Order.
I insert the Customers in to tblCustomer, where Primary Keys are populated. I then wish to retrieve the Customer PKs and insert: Customer.PK, Order in table tblOrder.
This is usually a two-step process. I fork the orchestration process into two paths,. The first one inserts/update tblCustomer. The second should insert/update tblOrder with the PKs that were JUST generated in tblCustomer. I can join tblCustomer on the second path to get them, but how do I know which path executes first? For this to work, the ins/upd on tblCustomer must finish first before the join takes place.
I hope I explained this clearly enough. I had a hard time searching for a similar issue here.
It sounds like what you are asking for is how to control your operations. In your example, you have tblOrder which is dependent on the insert/update results of tblCustomer. The easiest way to handle this is to create an orchestration that first runs a transformation job to populate tblCustomer which then follows calling a second transformation job to load the tblOrder. Here is the flow from a simplistic perspective:
Thank you so much for your replay. You understood perfectly what I was saying. Looks like the way you are suggesting is the way to go. Sometimes you are (I am) absorbed by the simplicity and compactness of Matillion code, that I think that anything can be done in one shot. The example below is what I had in mind (trying to keep all related code in a single transformation for compactness).
Of course the issue here is which part happens first. The insert into T_EMAILADDRESS or the JOIN of the same table to populate the bridge. Splitting it into two transformations is certainly the soluction.
