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}
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
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}'
I set the sink configuration as mentioned below:
After successful execution of pipeline the data loaded incrementally in db1: