Source Pre-load and Source Post-Load in Informatica

We can write the stored procedures to carry some action before the Source Qualifier executes and after the load is done on the Dimension table.

Example: Creating an Index on the Source table before the load and dropping the same index after the load is done on the target.

In our example, we have created two proceduresin the source database SCOTT – first one to create an index on the EMPNAME column of the source table EMP, and the second to drop the same index.

CREATE OR REPLACE PROCEDURE P_IN_EMP_NAME

AS

BEGIN

   EXECUTE IMMEDIATE 'CREATE INDEX IND_EN ON EMP (ENAME)';

END;

CREATE OR REPLACE PROCEDURE P_D_IND_EMP_ENAME

 AS

 BEGIN

     EXECUTE IMMEDIATE 'DROP INDEX IND_EN';

 END;

Note: Do not execute these procedures. We’ll do this task in Informatica by calling these procedures one after the other.

Steps:

Define a mapping   M_SRC_PRE_POST_LOAD.

Drag Source (EMP),Target  DIM_EMP to the mapping designer.

Create a SP Transformation T_SP_PRESOURCE_LOAD.

Import the stored procedure P_IN_EMP_NAME

Source Pre-load and Source Post-Load in Informatica

Edit the transformation. There are no ports.

In the Properties set the Connection Information to MEEN_SRC.

Give the Stored Procedure Type as Source Preload.

Assign the call text property as P_IN_EMP_NAME ()

Source Pre-load and Source Post-Load in Informatica

Note: Give the execution order if there are more than one SPs in the Pre Source load.

Similarly Create a SP Transformation T_SP_POSTSOURCE_LOAD

Import the procedure P_D_IND_EMP_ENAME.

Source Pre-load and Source Post-Load in Informatica

Edit the Transformation.

No ports.

Set the Properties:

Connection Information   MEEN_SRC

Call Text P_D_IND_EMP_ENAME ()

Stored Procedure Type Source Post Load

Source Pre-load and Source Post-Load in Informatica

Project the required ports from SQ to the TGT DIM_EMP.

Source Pre-load and Source Post-Load in Informatica

Save the repository.

Define Workflow.

Assign the connections (SRC,TGT,Transfn.)

Source Pre-load and Source Post-Load in Informatica

Save the repository.

Start the work flow.

Observe the Session Log.