Lesson 6	Data Transformation Services
Objective	Identify key features of Data Transformation Services.

Data Transformation Server Services in SQL Server

Data Transformation Services (DTS) is a utility that can transfer data between any OLE-DB data source.
While transferring the data, DTS can also transform the data. Depending on your data sources and your OLE-DB drivers you will have different limitations on that data being transferred.

Features of DTS

1) DTS is an OLE-DB based interface for defining and executing data transformations.

2) DTS can perform ActiveScript transformation using Javascript based transformation, Visual Basic based transformation, or simple field to field mapping transformation.

3) DTS uses a high speed bulk interface for fast loading using OLD-DB

4) DTS can access any OLE-DB or ODBC-based data sources, including Oracle, Access, Excel, DB2, and flat files

5) DTS can transfer both data and schema between data sources.

6) DTS Export and DTS Import wizards quickly move data and schema into and out of SQL Server.

7) DTS performs scheduled transformations using SQL Server Agent.

8) DTS uses a package containing workflows, tasks, and data to perform data transformations.

DTS Features and Services in SQL Server

(DTS) Data Transformation Services is a set of objects and utilities to allow the

automation of extract,
transform and
load operations

to or from a database. The objects are DTS packages and their components, and the utilities are called DTS tools. DTS was included with earlier versions of Microsoft SQL Server, and was almost always used with SQL Server databases, although it could be used independently with other databases.
DTS allows data to be transformed and loaded from heterogeneous sources using OLE DB, ODBC, or text files, into any supported database. DTS can also allow automation of data import or transformation on a scheduled basis, and can perform additional functions such as FTPing files and executing external programs.
Furthermore, DTS provides an alternative method of version control and backup for packages when used in conjunction with a version control system, such as Microsoft Visual SourceSafe.
DTS has been superseded by SQL Server Integration Services in later releases of Microsoft SQL Server though there was some backwards compatibility and ability to run DTS packages in the new SSIS for a time.

DTS Advantages

DTS’s strong point is its ability to perform multiple transformations on data. When transforming data, you can manipulate the source data before it is stored in the destination. This allows you to break complex transformations into multiple steps in order to better manage the transformation process. You can transform multiple data sources independently of one another, combining the results in a final step. On the other hand, DTS can break one record up to multiple destinations.

Data Transformation Services

DTS can move both the schema and data between the source and destination data sources, however, triggers, stored procedures, rules, defaults, constraints, and user-defined data types are not transfered between data sources.
The next lesson will explain how to use the DTS Designer to make a package.