Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. Data Transformation Services (DTS) addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by DTS connectivity. By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement solutions tailored to the specialized business needs of your organization.
A DTS package is an organized collection of connections, DTS tasks, DTS transformations, and workflow constraints assembled either with a DTS tool or programmatically and saved to Microsoft® SQL Server™, SQL Server 2000 Meta Data Services, a structured storage file, or a Microsoft Visual Basic® file.
Each package contains one or more steps that are executed sequentially or in parallel when the package is run. When executed, the package connects to the correct data sources, copies data and database objects, transforms data, and notifies other users or processes of events. Packages can be edited, password protected, scheduled for execution, and retrieved by version.
For more information, see Creating a DTS Package.
A DTS task is a discrete set of functionality, executed as a single step in a package. Each task defines a work item to be performed as part of the data movement and data transformation process, or as a job to be executed.
DTS supplies a number of tasks that are part of the DTS object model and can be accessed graphically, through DTS Designer, or programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation, and notification situations. For example:
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Note There are restrictions on this capability. For more information, see Copy SQL Server Objects Task.
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.
Because DTS is based on an extensible COM model, you can create your own custom tasks. You can integrate custom tasks into the user interface of DTS Designer and save them as part of the DTS object model.
For more information, see DTS Tasks.
A DTS transformation is one or more functions or operations applied against a piece of data before the data arrives at the destination. The source data is not changed. For example, you can extract a substring from a column of source data and copy it to a destination table. The particular substring function is the transformation mapped onto the source column. You also can search for rows with certain characteristics (for example, specific data values in columns) and apply functions only against the data in those rows. Transformations make it easy to implement complex data validation, data scrubbing, and conversions during the import and export process. Against column data, you can:
For example, you can change the type, size, scale, precision, or nullability of a column.
These functions can apply specialized transformations or include conditional logic. For example, you can write a function in a scripting language that examines the data in a column for values over 1000. Whenever such a value is found, a value of -1 is substituted in the destination table. For rows with column values under 1000, the value is copied to the destination table.
An example would be a function that reformats input data using string and date formatting, various string conversion functions, and a function that copies the contents of a file specified by a source column to a destination column.
For more information, see DTS Transformations.
You can define the sequence of step execution in a package with:
For more information, see DTS Package Workflow.
DTS is based on an OLE DB architecture that allows you to copy and transform data from a variety of data sources. For example:
DTS functionality may be limited by the capabilities of specific databases, ODBC drivers, or OLE DB providers. For more information, see Data Conversion and Transformation Considerations.
For more information, see DTS Connections.
DTS includes several tools that simplify package creation, execution, and management:
For more information, see DTS Tools.
DTS includes features for saving package meta data and data lineage information to Meta Data Services and linking those types of information. You can store catalog meta data for databases referenced in a package and accounting information about the history of a particular row of data for your data mart or data warehouse.
For more information, see Sharing Meta Data.