IICS | Incremental Load Using System Variable in Informatica Cloud

[@user-vd3ew4xn4w]

to_timestamp('$Lastruntime','YYYY-MM-DD HH24:MI:SS')-6/24 in oracle it is not working and it's showing >> ERROR: Prepare failed. : [ FnName: Execute -- [Informatica][ODBC Oracle Wire Protocol driver][Oracle]ORA-00907: missing right parenthesis ]

[@user-ku6vx3ih6w]

Hi Roshan do you have hands on experience on CAI also, can you upload videos on CAI, I saw your CDI videos those are really good and clear cut, you feel free please make a videos on CAI

[@ETLSTATION]

In my projects i have not worked in CAI yet , so first we want to cover all kinds of CDI mappings and scenarios then depending on my free time i may cover CAI as well but that will take time for sure

[@kaustubhshahane2460]

Hello sir...do we have video on FF to Oracle DB process in IICS....?

[@ETLSTATION]

i did not get your question. we have many videos for flat file to oracle if thats ur doubt and see latest video on incremental using un out param where i shown snowflake to oracle and flat file to oracle incremental load

[@SunilGoudo-sx1ft]

to_timestamp('$Lastruntime','YYYY-MM-DD HH24:MI:SS')-6/24 in oracle it is not working

[@ETLSTATION]

follow exactly i had shown it has to work

[@sunilgoudo6323]

Okay

[@ETLSTATION]

Hi Friends , we will learn how to use IICS system variables 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 : DATEADD(HOUR,-6,TO_TIMESTAMP('$Lastruntime','YYYY-MM-DD HH24:MI:SS')) for oracle sources : to_timestamp('$Lastruntime','YYYY-MM-DD HH24:MI:SS')-6/24

[@myjapanwala]

Hi sir, How to generate 3years date sir . exam: currentdate ~ 3years dates

[@ETLSTATION]

which database or informatica .

[@ETLSTATION]

use this function inside informatica when u doing , if its in source side then u need to follow databse syntax docs.informatica.com/data-catalog/common-content-for-data-catalog/10-5-4/transformation-language-reference/functions/add_to_date.html

[@myjapanwala]

@@ETLSTATION Sir I have been working IICS . Input file flat faile Example: Input:2023/11/12 I need output: 2023/11/12 to 2026/12/31 Please give solution sir

[@ETLSTATION]

does it have any one column ? your flatfile

[@myjapanwala]

​@@ETLSTATIONfile have one column sir that column have only one input date. example: column date type date & 2020/07/12.

[@anilkumarchilukuri7794]

Bro how to propagate columns from source to target, i have n number of translations between source and target, how can I propagate newly added column or delete old column to target at atime in iics

[@ETLSTATION]

kindly watch all my videos like dynmic task or remove symbols or split 1 field or scd typ 2 , you will get idea how to remove existing field and map new field . many times we created variable port and mapped new ports .

[@ETLSTATION]

you need to refresh the source and target if newly added column is not showing up

[@ETLSTATION]

here i created 1 video for all these doubts , watch it , it will premiere on monday 11.30 am IST , 12 AM MDT kzbin.info/www/bejne/q53Ieat3Z8afbas