Using Data Transformation Services (DTS), you can import and export data between multiple heterogeneous sources using an OLE DB-based architecture, and transfer databases and database objects (for example, indexes and stored procedures) between computers running Microsoft® SQL Server™ version 7.0. You can also use the data transformation capabilities of DTS to build a data warehouse from an online transaction processing (OLTP) system. You can build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis.
DTS components include the DTS Import Wizard, DTS Export Wizard, and DTS Designer, which are available through SQL Server Enterprise Manager. DTS also includes COM programming interfaces you can use to create customized import, export, and transformation applications.
A transformation is the set of operations applied to source data before it is stored at the destination during the process of building a data warehouse. For example, the DTS transformation capability allows calculating new values from one or more source columns, or even breaking a single column into multiple values to be stored in separate destination columns. Transformations, therefore, make it easy to implement complex data validation, scrubbing, and enhancement during import and export.
Data Transformation Services (DTS) allows you to import, export, or transform data in a process that can be saved as a package. Each package defines a workflow that includes one or more tasks executed in a coordinated sequence as steps. Tasks can copy data from a source to a destination, transform data using a Microsoft ActiveX® script, execute an SQL statement at a server, or even run an external program. Tasks can also transfer database objects between computers running SQL Server 7.0.
The DTS package can be created manually by using a language that supports OLE Automation, such as Microsoft Visual Basic®, or interactively by using the Data Transformation Services wizards or DTS Designer. After a DTS package has been created and saved, it is completely self-contained and can be retrieved and run using SQL Server Enterprise Manager or the dtsrun utility.
DTS packages can be stored in Microsoft Repository, providing the ability to record data lineage. This allows you to determine the source of any piece of data, and the transformations applied to that data. Data lineage can be tracked at the package and row levels of a table and provide a complete audit trail of data transformation and DTS package execution information in your data warehouse.
DTS Designer is a graphical design environment for creating and executing complex sets of data transformations and workflows, in preparation for moving data to a data warehouse. Experienced users can use DTS Designer to integrate, consolidate, and transform heterogeneous data from multiple sources. Packages created can be stored in a SQL Server msdb database, Repository, or a COM-structured storage file.
The visual objects used by DTS Designer are based on the DTS object model, an API that includes objects, properties, methods, and collections designed for programs that copy and transform data from an OLE DB data source to an OLE DB destination. This object model can be accessed through ActiveX scripts from within DTS Designer, and through external programs written in languages such as Visual Basic and Microsoft Visual C++®. You can also access custom programs through DTS Designer, and include their tasks and icons as part of the package. Because DTS Designer accesses an underlying programming model, it does most of the programming work for you.
| DTS Designer | Overview of Data Transformation Services |