Transforming data with Microsoft SQL Server 2000 DTS

Consolidating disparate data formats into single or multiple databases can be trying, especially on deadline. Microsoft SQL Server 2000 includes some tools that are specially designed to aid in the collection of data while insuring data integrity. These tools are called ETLs and one of them, DTS, is described in this tip from

    Requires Free Membership to View


Microsoft SQL Server 2000 includes a very powerful and flexible Extraction Transformation Loading (ETL) tool: Data Transformation Services (DTS). With DTS, Microsoft gives SQL Server 2000 the power to import, export, and transform data from disparate sources into single or multiple destinations. Microsoft groups all these elements in a DTS package.

To create and manage DTS packages, SQL Server 2000 includes several tools:

  • The DTS Import/Export Wizard creates simple DTS packages.
  • The DTS Designer is a graphical application for creating and editing DTS packages.
  • The Data Transformation Services node in the SQL Server Enterprise Manager console tree is used to view, create, load, execute, and manage DTS packages.
  • Package execution utilities are command-line utilities for managing DTS packages.
  • The DTS Query Designer is a graphical tool for creating SQL queries in DTS Designer.

DTS Packages

A DTS package is a collection of programmable objects that work together to accomplish a specific goal. You can use a DTS package to perform a variety of tasks:

  • Import and export data. A DTS package can import or export data to any OLE database or ODBC-compliant data source. Microsoft SQL Server 2000 includes drivers to connect to SQL Server, Oracle, Access, Excel, Visual FoxPro, dBASE, Paradox, HTML, text files, Microsoft Exchange server, Microsoft Active Directory, and Microsoft Analysis Services.
  • Transform data. Execute ActiveX scripts or scripts written in JScript, VBScript, or PerlScript.
  • Copy SQL Server objects. Copy data, indexes, triggers, and so on among two or more SQL Server databases.
  • Send and receive messages. Send messages from a DTS package to users or to other DTS packages.
  • Execute T-SQL commands. Automate the execution of T-SQL commands with DTS packages.

A DTS package contains a series of steps (tasks) that the package must complete. These tasks can be executed in sequence or parallel with one another. For example, if you want to copy data between two tables, you must first check that the destination table exists before copying data. If the table doesn't exist, the DTS package must create it before copying the data to it. The DTS package must execute these steps in sequence. If you want to copy data among several tables, you can run several parallel sequences to save time.

See examples of DTS package usage at InformIT.com.

This was first published in March 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.