This is a great new white paper out of Microsoft. In it they explain how to move a lot of data from flat files into MSSQL using SSIS. Some key take aways from the paper and my personal experiences loading data into MSSQL.

  • If at all possible get your source systems to provide flat files to the ETL processes. The popular ways like web services and ODBC are nice, but they tend not to perform as well when you start moving a lot of data.
  • Run SSIS on a separate server from the final destination database server. This is key for balancing the load and being able to easily up later.
  • Test, analyze, change, repeat. While the authors did use the standard off the shelf versions of the MSSQL and SSIS, they did have to tweak them to get the results they were looking for. It is critical to analyze your solution and make adjustments.

Provide other ETL best practices below.