IICS | Incremental Load using IN-OUT Parameter or Mapping Variable Method in Informatica Cloud

Hi Friends , we will learn how to use IICS in OUT parameter to do incremental loading in IICS . in this example , we will load data from snowflake to oracle table .

https://knowledge.informatica.com/s/a...

DDL/DML used for snowflake :
CREATE TABLE "CUST"
( "EMP_ID" NUMBER(38,0),
"EMP_NM" VARCHAR2(100 BYTE),
"STATUS" VARCHAR2(50 BYTE),
"SRC_CD" VARCHAR2(50 BYTE),
"CITY" VARCHAR2(100 BYTE),
"COUNTRY" VARCHAR2(100 BYTE),
"CREATE_DATE" TIMESTAMP (6),
"UPDATE_DATE" TIMESTAMP (6)
)
inserts :
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (10,'RAM','A','SAP','DELHI','INDIA','2023-05-01 06:10:36','2023-05-01 06:10:36');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (20,'STEVE','A','SAP','DALLAS','UNITED STATES','2023-05-01 09:15:20','2023-05-01 09:15:20');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (30,'ANGELA','A','SAP','MEXICO CITY','MEXICO','2023-06-02 10:12:45','2023-06-02 10:12:45');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (40,'RAVI','A','SAP','CANCUN','MEXICO','2023-06-02 11:23:35','2023-06-02 11:23:35');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (100,'PAT','A','SAP','CALGARY','CANADA','2023-07-13 13:24:34','2023-07-13 13:24:34');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (50,'DEV','A','SAP','CHENNAI','INDIA','2023-06-01 14:32:06','2023-06-01 14:32:06');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (70,'RAJ','A','SAP','PUNE','INDIA','2023-06-01 15:54:21','2023-06-01 15:54:21');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (80,'DIYA','A','SAP','INDORE','INDIA','2023-07-13 20:12:31','2023-07-13 20:12:31');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (90,'RANI','A','SAP','BHOPAL','INDIA','2023-07-12 23:47:41','2023-07-12 23:47:41');

Oracle DDL is same .

syntax used for Snowflake to run job when db servers is in MST/MDT timezone . please change value as per your timezone difference to GMT .

for snowflake sources :
TO_TIMESTAMP($$MAX_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS')

for flatfile sources :
TO_DATE($$MAX_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS')

Source data file data :
"EMP_ID","EMP_NM","STATUS","SRC_CD","CITY","COUNTRY","CREATE_DATE","UPDATE_DATE"
10,"RAM","A","SAP","DELHI","INDIA","2023-05-01 06:10:36","2023-05-01 06:10:36"
20,"STEVE","A","SAP","DALLAS","UNITED STATES","2023-05-01 09:15:20","2023-05-01 09:15:20"
30,"ANGELA","A","SAP","MEXICO CITY","MEXICO","2023-06-02 10:12:45","2023-06-02 10:12:45"
40,"RAVI","A","SAP","CANCUN","MEXICO","2023-06-02 11:23:35","2023-06-02 11:23:35"
100,"PAT","A","SAP","CALGARY","CANADA","2023-07-13 13:24:34","2023-07-13 13:24:34"
50,"DEV","A","SAP","CHENNAI","INDIA","2023-06-01 14:32:06","2023-06-01 14:32:06"
70,"RAJ","A","SAP","PUNE","INDIA","2023-06-01 15:54:21","2023-06-01 15:54:21"
80,"DIYA","A","SAP","INDORE","INDIA","2023-07-13 20:12:31","2023-07-13 20:12:31"
90,"RANI","A","SAP","BHOPAL","INDIA","2023-07-12 23:47:41","2023-07-12 23:47:41"


#informatica #iics #informática #tutorial #etltesting #datawarehouse #sql