azurecloudlookuporacle-adf

Incremental load with lookup and timestamp in adf


I want to do in incremental load from an informieren dB to azure sql db. I used a lookup to get the latest time stamp from the sql db because full load has already been done and I want to use the lookup output to get do incremental load. Here is my query

Select * from informixdb where select max(systemtimestamp) from informixdb) > @{activity('LookupSYS_TIME).output}


Solution

  • To load the data incrementally You can follow below procedure:

    In my case I am incrementally loading the data from db database to db1 database. I run below query in Lookup activity:

    SELECT MAX(systemtimestamp) AS MaxTimestamp FROM student
    

    enter image description here

    After successful run of lookup activity added copy activity selected the db as source and added below query to the query tab:

    Select * from student where systemtimestamp > '@{activity('LookupSYS_TIME').output.firstRow.MaxTimeStamp}'
    

    enter image description here

    I set the sink configuration as mentioned below:

    enter image description here

    After successful execution of pipeline the data loaded incrementally in db1:

    enter image description here