Understanding Data Transformation Services

Data Transformation Services (DTS) provides the functionality to import, export, and transform data between Microsoft® SQL Server™ and any OLE DB, ODBC, or text file format. Using DTS, it is possible to:

• Build data warehouses and data marts in Microsoft SQL Server by importing and transferring data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis.
• Create custom transformation objects that can be integrated into third-party products.
• Access applications using third-party OLE DB providers. This allows applications, for which an OLE DB provider exists, to be used as sources and destinations of data.

OLE DB provides:

• Access to native OLE DB providers such as SQL Server, Microsoft Excel, Microsoft Access, and Oracle.
• Access to ODBC data sources for which you do not yet have an OLE DB provider. This is done using the Microsoft OLE DB Provider for ODBC with an ODBC driver for the ODBC data source.
• Access to ASCII fixed-field length text files and ASCII delimited text files using the built-in DTS Data Pump flat file OLE DB provider (Dtsffile.dll).

DTS also provides support for:

• High-speed nonlogged inserts (bcp) into SQL Server version 7.0.
• Creating customized transformations.
• Transferring complete database objects between source and destination SQL Server 7.0 data sources. The Transfer SQL Server Objects task can be used to transfer all of the metadata and data for some or all of the objects in one SQL Server 7.0 database to another SQL Server 7.0 database. For example, the Transfer SQL Server Objects task can be used to move a table with all of its associated index, constraint, rule, default, and trigger definitions and the existing rows in the table. The Transfer SQL Server Objects task also can be used to transfer the definitions of objects such as views and stored procedures.

Importing and Exporting Data

Importing and exporting data is the process of exchanging data between applications by reading and writing data in a common format. For example, DTS can import data from an ASCII text file or Access database into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination, such as an Excel spreadsheet.

Transforming Data

A transformation is the set of operations applied to source data before it is stored at the destination. For example, DTS 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 make it easy to implement complex data validation, scrubbing, and enhancement during import and export.

Transferring Database Objects

When using heterogeneous data sources, the built-in facilities of DTS only move table definitions and data. To transfer other objects such as indexes, constraints, and views you must use methods such as specifying tasks that execute the SQL statements needed to create these objects on the destination data source. However, if both the source and destination are SQL Server 7.0 data sources, you can define a Transfer SQL Server Objects task to transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to transferring the data.

Note To upgrade SQL Server 6.5 databases, use the SQL Server Upgrade Wizard.

See Also