Building a Successful ETL Project

by Allan Mitchell 28 Oct 2008 09:00

When we are building ETL projects we need to do some planning up front to make sure we have all the things we need to make it a success. Here are some of the things we consider when embarking on an ETL Project. There are two lists. The first is around an ETL Project in general and the second is around things we think about specifically for SSIS.

  • Review target data model
  • Identify source systems (Owners, RDBMS types, Permissions)
  • Analyze and profile source systems (usge patterns, Windows of opportunity)
  • Document source data defects and anomalies (known issues, data profiling)
  • Define business rules required for the project (What is it we are trying to achieve)
  • Define data quality rules (Thresholds, OOR values etc)
  • Develop mappings for the target tables
  • Integrate business and quality rules with mappings
  • Lineage
  • Security
  • Compliance
  • Available Skills
  • Legacy items (Is anything about to retire? Can you still get drivers?)

Rules around specifics

  • Good naming convention for objects
  • Break down packages into manageable pieces of work (Scalability,Manageability, restartability)
  • Consider restarts for the package
  • Consider touching down on disk after screens (Raw Files)
  • Logging (Use of event handlers)
  • Do not hard code values, use Package Configurations
  • If you use Package Configurations then use Indirect Configurations

Add comment

  Country flag

  • Comment
  • Preview