Data Transformation Services Enhancements

SQL2000 Whats New

What's New

Data Transformation Services Enhancements

Microsoft® SQL Server™ 2000 introduces these Data Transformation Services (DTS) enhancements and new features:

New Custom Tasks

New DTS custom tasks, available through DTS Designer or the DTS object model, allow you to create DTS packages that perform tasks or set variables based on the properties of the run-time environment. Use these tasks to:
  • Import data from, and send data and completed packages to, Internet and File Transfer Protocol (FTP) sites.

  • Run packages asynchronously.

  • Build packages that send messages to each other.

  • Build packages that execute other packages.

  • Join multiple package executions as part of a transaction.

For more information, see Building a DTS Custom Task.

Enhanced Logging Facilities

DTS package logs save information for each package execution, allowing you to maintain a complete execution history. You can also view execution information for individual processes within a task.

You can generate exception files for transformation tasks. When you log to exception files, you can save source and destination error rows to a file through the DTS OLE DB text file provider and re-process the error rows.

Saving DTS Packages to Visual Basic Files

DTS packages now can be saved to a Microsoft® Visual Basic® file. This allows a package created by the DTS Import/Export Wizard or DTS Designer to be incorporated into Visual Basic programs or to be used as prototypes by Visual Basic developers who need to reference the components of the DTS object model. For more information, see Saving a DTS Package.

Using the Multiphase Data Pump

A new multiphase data pump allows advanced users to customize the operation of the data pump at various stages of its operation. You can now use global variables as input and output parameters for queries. For more information, see Multiphase Data Pump Functionality..

Using Parameterized Queries

You can now use parameterized source queries in a DTS transformation task and an Execute SQL task. In addition, DTS includes an option for saving the results of a parameterized query to a global variable, allowing you to perform functions such as saving disconnected Microsoft ActiveX® Data Objects (ADO) recordsets in DTS. For more information, see Using Parameterized Queries in DTS.

Using Global Variables to Pass Information Between DTS Packages

You now can use the Execute Package task to dynamically assign the values of global variables from a parent package to a child package. Use global variables to pass information from one package to another when each package performs different work items. For example, use one package to download data on a nightly basis, summarize the data, assign summary data values to global variables, and pass the values to another package that further processes the data.