DTS Basics

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.

DTS Packages

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.

DTS Tasks

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:

• Importing and exporting data.

  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.

• Transforming data.

  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.

• Copying database objects.

  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.

• Sending and receiving messages to and from other users and packages.

  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.

• Executing a set of Transact-SQL statements or Microsoft ActiveX® scripts against a data source.

  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.

DTS Transformations

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:

• Manipulate column data.

  For example, you can change the type, size, scale, precision, or nullability of a column.

• Apply functions written as ActiveX scripts.

  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.

• Choose from among a number of transformations supplied with DTS.

  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.

• Write your own transformations as COM objects and apply those transformations against column data.

For more information, see DTS Transformations.

DTS Package Workflow

You can define the sequence of step execution in a package with:

• Precedence constraints that allow you to link two tasks together based on whether the first task executes, executes successfully, or executes unsuccessfully. You can use precedence constraints to build conditional branches in a workflow. Steps without constraints are executed immediately, and several steps can execute in parallel.
  
  
• ActiveX scripts that modify workflow. For more information, see Using ActiveX Scripts in DTS.

For more information, see DTS Package Workflow.

Connectivity

DTS is based on an OLE DB architecture that allows you to copy and transform data from a variety of data sources. For example:

• SQL Server and Oracle directly, using native OLE DB providers.
  
  
• ODBC sources, using the Microsoft OLE DB Provider for ODBC.
  
  
• Access 2000, Excel 2000, Microsoft Visual FoxPro®, dBase, Paradox, HTML, and additional file data sources.
  
  
• Text files, using the built-in DTS flat file OLE DB provider.
  
  
• Microsoft Exchange Server, Microsoft Active Directory™ and other nonrelational data sources.
  
  
• Other data sources provided by third-party vendors.

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 Tools

DTS includes several tools that simplify package creation, execution, and management:

• The DTS Import/Export Wizard, which is used to build packages to import, export, and transform data, or to copy database objects.
  
  
• DTS Designer, a graphical application that lets you construct packages containing complex workflows, multiple connections to heterogeneous data sources, and event-driven logic.
  
  
• The Data Transformation Services node in the SQL Server Enterprise Manager console tree, which is used to view, create, load, and execute DTS packages, to control DTS Designer settings, and to manage execution logs.
  
  
• Package execution utilities:
  • The dtswiz utility starts the DTS Import/Export Wizard by using command prompt options.
    
    
  • The dtsrun utility runs a package from a command prompt.
    
    
  • The DTS Run utility (dtsrunui) allows you to run a package using dialog boxes.
• DTS Query Designer, a visual database tool that makes it easy to build queries in DTS Designer.

For more information, see DTS Tools.

Meta Data

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.

See Also

Programming DTS Applications