ETL and Data Warehousing: a Complex Challenge

Integration Services, a component of SQL Server 2005, is a middleware utility that supports the data integration process generically referred to as extract, transform, and load (ETL). ETL processes generally involve the following:

• Connecting to and extracting data from multiple, often incompatible sources, such as transactional sales- or order-tracking systems, customer information and call-center tracking databases, and file-based Web logs
• Data processing logic; for example, ETL processes typically aggregate data from various sources, transform those data into a common format, resolve inconsistencies among the data sources (e.g., different column-naming conventions, different units of measure for particular fields), and discard or summarize information to compress the output
• Connecting to and loading the processed data into one or more destination databases
• The workflow that dictates the logical steps in the ETL process and the sequence of those steps. For example, the ETL workflow