Load Flag Update in staging table

[edward_m]

All,

I am loading the records from satging table into target table.I select the records from staging based on load_flag is null and process those records and load into target.After the process i want to update the load_flag in staging table to 'L' or 'F' i.e if the staging record succesfully inserted into target table then update the load_flag column for that record in staging table with 'L'..if record failed to insert into target table then 'F'.

Please suggest me the best way to do the above process.

Thanks in advance.

[thumsup9]

You can specify Reject Link in the transformer just before writing to your target.Reject links output rows that have not been written on any other output links from the Transformer stage. This way you can send these rows down the reject link to a flat file. In second job,using the flat file update the staging table, when key found the Load Flag ='F' else 'L'

[ray.wurlod]

The safest way is to do two passes. One does the load, and captures any rejects. The second pass either performs a lookup against the target table to set "F" (all others in the staging table that are null are set to "L") or, if you have captured rejected rows, reads those keys to set the flag to "L" or "F" depending on appearance of that key in the rejects data.