Data Transformation Services (DTS) allows you to import, export, or transform data in a single process. The definition of this process can be saved in an object called 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. A Transfer SQL Server Objects task can also transfer database objects between computers running Microsoft SQL Server™ version 7.0.
DTS packages contain these types of objects:
Define each OLE DB data source that is a source or destination of data.
Define each work item, such as executing an SQL statement, copying the contents of a table, or executing an ActiveX script.
Define the sequence in which the task objects are executed. Step objects also contain precedence constraints that define how the results of one task determine whether subsequent tasks will execute. For example, step 2 can be defined with an On Success precedence constraint for step 1, so that step 2 is executed only if Step 1 completes successfully.
This illustrations shows the steps and tasks of a package.
The SQL Server components used to define packages include:
A graphical user interface (GUI) in SQL Server Enterprise Manager used to interactively design and execute DTS packages.
Wizards that ease the process of defining DTS packages to import and export data between heterogeneous OLE DB data sources. The wizards also assist with defining transformations to be made on the data. The wizards can also be started from the command prompt using the dtswiz utility.
DTS provides a set of OLE Automation interfaces and a set of COM interfaces you can use to create customized import, export, and transformation applications in development systems that support OLE Automation or COM.
After a DTS package has been defined it can be executed using:
DTS provides an extensible COM-based architecture, allowing the creation of new data transformations based on data warehouse design requirements. Complex transformation and data validation logic can be implemented using the ActiveX scripting engines: Microsoft Visual Basic® Scripting Edition and Microsoft JScript®. These scripts can invoke methods from any COM object to modify or validate the value of a column in a table, or perform any operation supported by the VBScript or JScript languages.
DTS implements a pass-through architecture to ensure that all the functionality of source and destination OLE DB data sources is available. DTS transmits all SQL statements specified in DTS tasks unchanged to the source or destination OLE DB data sources. Statements or stored procedures tested against an OLE DB provider work the same way in a package as they do from an OLE DB-based application or utility.
| Data Transformation Services Import and Export Wizards | Programming DTS Applications |
| DTS Designer |