So far I have done full load from source to DW(dimension and fact tables), now the requirement is to load only newly added and updated records in Datawarehouse. creation_date on source helps me find the newly added and updated records, can someone help me how do I load this on to datawareh

Hi Sree,

you can use the lookup and conditional split data flow tasks to identify the delta records. records will be divert to the appropriate stream.

Make sure your source query is only the distinct key coming to the lookup.

please have a look following example.

http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx

Thanks,

Zaim Raza.

persist the creation_date in your ETL framework for each table

extract the new data from the source into a staging table

left join the staging table and the destination table on the business key (this way your data flow will only contain new rows and rows that MIGHT HAVE changed)

use one of the patterns (SCD task, checksum + merge + conditional) to check the existing rows for changes.

Upsert