✍ Kashif M
Trends

Monday, 9 September 2013


Source Pre-load and Source Post-Load in Informatica


By on September 09, 2013




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
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
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
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
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
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
Source Pre-load and Source Post-Load in Informatica

Save the repository.

Start the work flow.

Observe the Session Log.

Kashif
mkashu: Source Pre-load and Source Post-Load in Informatica
Review : Kashif | Kashif
Update: September 09, 2013 | Rating: 4.5

Comment for "Source Pre-load and Source Post-Load in Informatica"

0 comments

Post a Comment

Blog Archive