Incremental Load by Date
Note: Combine this with your Incremental Load by ID.
Example of use within load table:
1. Create 3 parameters
Current_dt
Full_load_flag
Last_run_dt
2. Create two custom stored procedures (Code for procedures attached)
update_current_dt_parameter
update_last_run_dt_parameter
3. Add calls for the SP’s in the job that populates your warehouse, something like below:
JOB load data warehouse:
update_current_dt_parameter --very first if workflow
…..some tasks….
update_last_run_dt_parameter --very last if workflow
4. In “Where” clause in the load table add something like this:
WHERE (<timestamp from loadtable>
BETWEEN CONVERT(datetime, '$PLast_run_dt$')
AND CONVERT(datetime, '$PCurrent_dt$'))
OR '$PFull_load_flag$' = 'Y'