- Separate mapping/session to get all pid from a file and load the pid's in a relational table(load_result table). The table could consist of the following fields.. pid, datawarehouse transaction datetime, completion_status(with a status of 'I').
- Create a mapping/session to ouput a param flat file. The mapping should have a logic to pick the (min) of pid where the completion_status is an 'I'. Snapshot of lod_rlst table before the staging or fact load commences
1 I 01/01/2009 12:00:00:000 AM
- Call your next dummy session to load staging or fact tables from post-session command(components tab). The Link between param file(step 2 above) and next dummy session (step 3) should say. Previous_task = succeeded and previous_target_success_rows > 0. (We want to call the next session to load data warehouse tables, if only the param file has any pid in it).
- After data is loaded through staging/facts, at the end of all successful staging/facts sessions, create another dummy session with a post_sql to update the completion_status for the pid that was just loaded from 'I' to 'C' (Incomplete status to a Complete Status). Now delete from load_result table where completion_status is 'C'. In post_session success command of this dummy session, it should call step 2 above. Snapshot of load result table after a load is completed successfully but before it's deleted from lod_rslt table
|1||C||01/01/2009 12:00:00:000 AM|
5. The link in step 2 will help the process continue if there are any pid's left in lod_rslt table. If all pid's are done, the session should complete gracefully and send an email.