How to do an incremental load by date

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'