SSIS Performance Optimization : Reduce time to load data into a SQL Server relational data warehouse without modifying packages

In SSIS, generally the focus remains to develop the packages as efficiently as possible using the most known or practiced best practices. But less attention is given towards the DB structure comparatively which can indirectly reduce the time of execution of load packages.

During the Performance optimization / Load testing phase, when the limits of SSIS package optimization reaches it's edge; then comes to rescue the optimizations that can be done at the database level.

I have been evident of development environments, where the relational data warehouse contains all the facts and dimensions with all foreign-key constraints enabled along with whatever indexes created to boost the read operations for SSAS during cube processing.

A typical ETL cycle goes as follows: Extract - Maintenance (Backup / Archive / Stage) - Transform - Maintenance (Backup / Archive / Stage) - Load - Maintenace (Backup / Archive / Stage). Prior to the load phase, for eg. if the indexes are disabled instead of dropping then there are two advantages:

1) By disabling the indexes prior to the load, you avoid all the overhead required to maintain the indexes.
2) By using the disable/enable method, you do not have to maintain scripts to recreate the indexes following the data load.

An index can be disabled by using the ALTER INDEX statement as follows:
ALTER INDEX { index_name ALL } ON objectname
DISABLE [ ; ]

To enable an index, it must be rebuilt to regenerate and populate the B-tree structure. You can accomplish this by using the following command:

ALTER INDEX { index_name ALL } ON objectname
REBUILD [ ; ]