Load fact table incrementally

Question

What is the best way of loading a fact table incrementally using SSIS?

Answer 1

Depends upon the schema of your source table. If you've an auto incremented ID, store it's value in a control table and do an import where ID > your last imported one. Same sort of thing for date columns store it in a control table and import where date > last imported date.

Depending on your version of SQL, you could also look into using change data capture (SQL2008), in which you can do things like give me all the changes since ? or give me the net effect.

Answer 2

Have a look at this question.