Let's stick with Kimball mathod for a minute.

These PK/Fk pairs are surrogate keys not the natural keys from OLTP. They should be built during initial OLAP load and maintained in the subsequent ETL process.

Which means in dimensional table, if it is type2, it gets a new set of PK with every update. Thus, comes with the need of remapping the fact table's FK to that new FK.

Now, to implement this theory in SSIS successfully is something I would like to see.

Anybody has the experiece implementing, say 3 to 5 fact tables with a dozen dimention tables and the total size of the db is in 100 GB range?

How long will a historical load take?