Support Wikipedia

Sunday, April 19, 2009

Looping in Informatica

  1. 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').
  2. 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

    01/01/2009 12:00:00:000 AM

  3. 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).
  4. 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

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.


  1. This part seems crucial: "In post_session success command of this dummy session, it should call step 2 above"

    How do I call step 2? That sounds like a GOTO statement of some sort where step 4 goes back to step 2.

  2. Step 5 could call step 2 via pmcmd from command task. Step 2 in turn would determine if there are any more pid's that needs to be processed.