Best design pattern -- SSIS package for DWH load

Question

I need help in designing SSIS packages for our DWH load.

I have two star-schema model with following details

1st Model --> 5 dimension and 5 fact tables respectively
2nd Model --> 5 dimension and 1 fact tables respectively

I have five different source system from where I need to populate data into these tables.

Based on the above requirements I have thought of designing the package like this:

there will three packages and which will do the following:

1. First package will extract the data from source systems to staging table (SQL Server tables) with all the necessary transformation.
2. Second package will load the data to all the dimension tables.
3. Third package will load the data to all the fact tables.

Please let me know if the above design/architecture will work for this DWH load or do I need to do some modification.

Answer 1

This is quite hard to answer because ultimately if it works then it is correct. There are only varying degrees of "correctness" (is that a word?) or answers which are more (or less) elegant depending on your viewpoint.

However, as a general rule and speaking for myself I have always found it more elegant to load the data into a staging area and then distribute Dimensions and then Facts via Procedures. The work is then performed within the context of the target database and not by the package. The package acts to control the flow.

Also, I would avoid splitting the tasks into more than one package unnecessarily. Of course there may be other considerations that may affect this decision. E.g. Multiple data updates arriving from different sources at different times, but even then I would tend to stage and update at once.