ETL and Data Warehousing: A Complex Challenge

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

Data extraction. ETL processes must connect to and extract 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. 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.

Data output. ETL processes must connect to and load the processed data into one or more destination databases.

Workflow. An ETL workflow specifies the steps in the process and the sequence of those steps. For example, the ETL workflow could define setup steps (moving Web log files to a file share used for staging and combining logs from different Web servers into a single file) and specify the types of data transformations to apply.