ODI - Create a date variable and use sysdate in a package

Usecase: we will create refresh variable which will store sysdate data and use in a map field. We will map source Location table to target location table with ETL_date field and use in a a package.

Source table has:

• Loc_id
• Street_address
• Postal_code
• City
• State_province
• Country

Target table has:

• Loc_id
• Street_address
• Postal_code
• City
• State_province
• Country
• ETL_date

Implementation Steps:

Step1: Create oracle technology physical and logical schema and context in topology

Step2: create model of the logical schemas and have data stores.

Step3: create a project and create new mapping

Drag and drop source and target tables

Map them auto by name

Step4: create a variable and go to refreshing section and choose schema and write the select query.

Select TO_CHAR(sysdate, 'YYYYMMDD HH24:MI:SS') from dual

Step5:Go back to the mapping and select Target ETL_date field and edit expression and use creates variable.

Save

Step6:  create a new package and drag and drop map and variable to package

Link ok between them

Step7: Go to the mapping and physical tab and choose context and Knowledge modules as below:

LKM for default: LKM SQL to SQL

IKM for target: IKM SQL Control Append

Flow control : False

Step8: create a step scenario and save and run

Step9: check the status from Operator table and also from your target db table.