I?ve a job which extracts some data from source DB (Oracle) to a target DB (MS SQL 2005).
By default, this job loads all data from source DB. But I?d like to implement a delta (differential) load, to take only data that have been modified or inserted since the last execution of the job.
This delta load should be based on a comparison between the date of the last execution, and a timestamp contained in the source DB (date of last modification). If the timestamp is more recent than the last execution, then the record must be extracted. If not, the record is ignored.
Could you please indicate me how such a mechanism can be implemented thanks to TOS 3.1.3 ?
Here is one way to proceed, maybe not the best but it can put you on the track :
At the execution time, write the current date in a file. (for example at the end of the job)
At the beginning of the job, go fetch in this file the date of last execution, set it as a globalvar.
In the tOracle_Input, modify the query by adding a condition in the end " WHERE TIMESTAMP > " + globalMap.get("date_of_last_execution")
You also can implement the filter condition in a tFilterRow, this might avoid to manually do some date conversion.
