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 .

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

[VenkatReddy-zokx]

Roshan thanks for the video, but is there any way to load incremental data without any audit columns(date columns) in source. if it is there kindly make a video on it. thanks in advance

[Adikes_InfoTech]

Hi Roshan, I have a small doubt, I hope you can reply on this. Which authentication type will use in real time for snowflake connection in IICS? And for incremental load if the source records are deleted permanently after loading into target. How can we check those deleted records in Target and delete as well from target table?

[bradyb]

so we can use the IN-OUT parameter field as a audit column and can be used this incremental loading purpose right?.is it ok if the text file is deleted after the mapping task is finished and updated variable value in inout parameter still persistent or wiped ?

[Adikes_InfoTech]

I just want to tell you one thing, please improve your mic quality. Apart from this all your videos are superb.... It really helps a

[dakshssarapuregamingandpia]

Task getting failed and in session log This is the error massage. (I have used IN-OUT Param). argument 2 to function TO_TIMESTAMP_NTZ needs to be an integer, found: ''YYYY-MM-DD HH24:MI:SS'' Video no 67. How to resolve?

[ajayaj]

Most of the places audio is not clear 😢😢😢