Hi Sam:

The complete architecture of a DW loading process is too complex to resume it in a forum post. What I can give you are only general tips:

- I think is Ok to have a package to load the Staging Area since you should only perform the "E" part of the ETL.

- I would separate the dimensions into "slow changing dimensions" and "dimensions". I don´t know if you manage the concepts but if not you could easily find several articles on this topic.

- I would load every fact table individually in a separated package.

- I would create a log table (or not if you’re working with SSIS 2012). In this table I would log the name of the package, starting and finish execution time, number of rows updated, inserted or deleted and a comment if you want.

- I would also create “CreateDT” and “UpdateDT” in every Fact and Dimension table. This is very useful if you have and error and you want to repair your execution.

I think your initial design allows error correction, you just need to use a strategy like the create and update columns above. There are several ways to handle errors, at job level, package level, component level, it depends on many factors like the relevance of the error.

Finally I would execute the sequence of packages using a scheduled Agent Job in the SQL Server.

Good luck and have fun implementing your solution