Transforming Data with Microsoft SQL Server 2000 DTS

• By Luis Garcia
• Nov 26, 2003

Like this article? We recommend
Microsoft SQL Server 2000 DTS [Data Transformation Services]

DTS Overview

Building a data warehouse is a complex and difficult process, and probably the most time-intensive task of this process is data loading. Usually data is spread throughout the organization and stored in many different locations and formats: spreadsheets, text files, databases, and so on. Database administrators need to deal with data collection, data integrity, data transmission, and data loading problems. Extraction transformation loading (ETL) tools help the database administrator deal with these issues.

Microsoft SQL Server 2000 includes a very powerful and flexible ETL tool: Data Transformation Services (DTS). With DTS, Microsoft gives SQL Server 2000 the power to import, export, and transform data from disparate sources into single or multiple destinations. Microsoft groups all these elements in a DTS package.

To create and manage DTS packages, SQL Server 2000 includes several tools:

• The DTS Import/Export Wizard creates simple DTS packages.

• The DTS Designer is a graphical application for creating and editing DTS packages.

• The Data Transformation Services node in the SQL Server Enterprise Manager console tree is used to view, create, load, execute, and manage DTS packages.

• Package execution utilities are command-line utilities for managing DTS packages.

• The DTS Query Designer is a graphical tool for creating SQL queries in DTS Designer.