SSIS Package Design

Question

What is the best way to design a SSIS package? I'm loading multiple dimensions and facts as part of a project. Would it be better to:

1. Have 1 package and 1 data flow with all data extract and load logic in 1 dataflow?
2. Have 1 package and multiple data flows with each data flow taking on the logic for 1 dimension?
3. Have 1 package per dimension and then a master package that calls them all?

After doing some research 2 and 3 appears to be more viable options. Any experts out there that want to share their experience and/or propose an alternative?

Answer 1

You could also consider having multiple packages called by a SQL Server Agent job.

Answer 2

I would often go for option 3. This is the method used in the Kimball Microsoft Data Warehouse Toolkit book, worth a read.

http://www.amazon.co.uk/Microsoft-Data-Warehouse-Toolkit-Intelligence/dp/0471267155/ref=sr_1_1?ie=UTF8&s=books&qid=1245347732&sr=8-1

Answer 3

Microsoft's Project Real is an excellent example of many many best practices:

• Package Design and Config for Dimensional Modeling
• Package logging
• Partitioning

It's based in SQL 2005 but is very applicable to 2008. It supports your option #3.