commit after each row

[kennyapril]

In my requirement a table is loaded with a primary key and in the middle of the job this primary key is taken as another field to get loaded.

I need to do lookup with the target to get this primary key and use this to load another field.
Please suggest how can I do this with commit each row after loading and then do lookup.
My target is oracle enterprises stage.



Thanks

[kennyapril]

I tried to do sparse lookup with the target to get the primary key value which is to be loaded but that did not help?

I need to load a record and do lookup with the record and load second record and then lookup second record........

Also committed after 1 row in the target but still the field is not getting populated in the target.


please suggest any changes if required?



Thanks

[ray.wurlod]

Can this field be legally populated into the target?

Is any error/warning message thrown?

[kennyapril]

This field cannot be populated into the target before loading the parent record.

In this scenario the records from the source enter as each group which form unique with hiername and effdate.

Like first 5 records belong to one group and next 4 records next group and 6 records third group and so on.....

First the parent record should be loaded which will be assigned a primary key and there is an other field which is parentkey which will be 'none' for the first record.
Next the children has to be loaded and in the middle the primary key of the parent is pulled and loaded as parentkey to the children and the flow should continue to all the records.

In a single sentence load the parent first and take the primary key and assign it as parentkey to the children to next level and then next level....

This has to be done in a single job.


No error or warnings are seen but the parentkey is empty after the load is completed which should have the primarykey value of the parent.



Thanks

[chulett]

This has to be done in a single job.

OK, I'll bite... why?

[SURA]

Why you need to do it in a single job? Is there is any restrictions?

At the run time you can't fetch it for each row.

DS User

[kennyapril]

This is a typical typeII job where we capture changes and update the records.
But before CDC we need to send the records with the parent key which we get after doing lookup with the target.

If an other job is developed in the above kind of scenario we cannot compare the keys and also it may not pick up the right parent.

This is basically an organization related area which has different levels of hierarchies.