Incremental Load Using Timestamp Variable

273 March 7, 2024

Incremental Load Using Timestamp Variable

Incremental Load:- Incremental data loading is the process of loading the selective data which is either updated or created new from source system to the target system. This is different from full data load where entire data is processed each load.

Procedure: –

  • Create one Source table in the oracle as shown below.

  • Login to IICS and click on new mapping. Click on create to create new mapping and rename the mapping of your choice for example: m_incr_time.

  • Go to Actions and click on parameters panel.

  • Create one in-out parameter as shown below and click on Ok.

Source: –

  • Go to workspace and click on source.
  • In the properties go to sourceand configure the connections by selecting the required connection name.
  • In the Object select the SQL Query and write the query shown below and click on ok.

Expression: –

  • Drag the Expression transformation from the left side of design menu and connect it to the Source.

  • Check the incoming fields
  • In Expression option click on + button to create variable or output fields.
  • Now create an Output field by selecting the below options and click on ok.

  • Configure the field by assigning the formula and click on ok.
  • SetMaxVariable ($$p_maxdate, ORDER_DATE)

Target: –

  • Connect the Targetand go to properties.
  • Incoming Fields: check all the incoming fields coming from expression.
  • Target: Give Target connections.

  • Object: Click on select option and select existing as Target Object and select table name (eg: tgt_max) and click on ok.

  • Save the mapping and create the Mapping Task.
  • Click on newto create the mapping task and give the proper task name and specify the Run time environment.

  • Click on select and select the mapping from the mapping list as shown below.

  • Click on next. Click on run to run the mapping. Go to my jobs and check the status.

  • Check all the 10 records from order detail will be loaded into the tgt_max table.

  • Go to oracle and check the target table shown below.

  • Now let’s insert a new record and see how the mapping behaves.
  • INSERT INTO orderDetail VALUES(11,’calculator’,’10-03-2024′,1000,4);
  • We can now observe that the source table

  • After inserting the new record run the mapping again and see the my job status.

  • Check the new record from order detail will be loaded into the tgt_max table or not.

  • Go to oracle and check the target table shown below.

Mapping: –