Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Data Transformation Services (DTS) Overview

Last updated Mar 28, 2003.

Most often data is entered into Microsoft SQL using applications and other programs. But at times you need to enter large amounts of data, most often from a file or other data source. Microsoft SQL Server provides several means such as the Bulk Copy Program (BCP) and the BULK INSERT statement to get large amounts data in and out of the database. But by far the easiest and most flexible method in SQL Server 2000 is using Data Transformation Services (DTS). This tool can be used in two main ways, although they're just different views of the same process. You can use the Data Transformation Wizard, or you can create and edit the groups of commands in DTS (called "packages") directly. I'll discuss the DTS Wizard first, and then I’ll take a moment to examine packages. We’ll just do a high-level discussion of this tool in this tutorial. To really delve into the details we’ll use practical applications to move data back and forth.

The Data Transformation Wizard

The simplest way that you can use DTS is using a Wizard. You can access a DTS Wizard in Enterprise Manager by right-clicking an object such as a table or database, selecting All Tasks, and then selecting Import/Export Data from the menu that appears. This starts the Data Transformation Wizard, which guides you through transferring data into and out of SQL Server, step by step.

The first step of the DTS Wizard prompts you to select a source of the data. You can select SQL Server as the source, which usually means that you're going to export data. You can also select another source, such as text files, Microsoft Excel worksheets, Oracle databases, etc., which could indicate that you're importing data.

Next you select the objects within that first choice, which depends on the source of the data. You can import or export tables, views — even the results of stored procedures.

The next step is to select the destination of the data. Just like the selections presented for the source section, this choice can consist of many different types of data storage.

This choice of both source and destination types is one of the main strengths of DTS — you can use it not only for SQL Server, but also between other data engines. For instance, you can export data from an Oracle database to a Microsoft Access database file, or from a text file into a FoxPro database file.

The next choice the wizard presents is to transform the data. This exposes another powerful feature in DTS: the ability to modify the data as it is being transferred. These transforms can even be coded, such that every third letter could be changed to the number five, and so forth.

Once you're done making all these selections, the wizard allows you to save the settings to a file, a database, or even a special type of database called a repository.

Saving these settings brings us to the next type of DTS use, which is really just accessing all the settings I've been talking about directly — something Microsoft calls a DTS package. Many DBAs create their initial package from a wizard and then edit it to fit a broader purpose.

Packages

Packages contain all the elements necessary to perform the DTS operations: connections to the data sources and destinations, as well as tasks to perform along the way. You can send mail, write files, run programs, and transform data. When you string all this together you create a workflow by using success and failure conditions.

A word of caution here is warranted. One of the objects a package can contain is other packages, linked with a workflow. I’ve seen Franken-packages that string literally hundreds of packages together. While this can work, this type of system can be difficult to manage and especially to troubleshoot. While DTS is a fantastic tool, it shouldn’t substitute for a well-designed enterprise management paradigm.

You can access the packages in the Data Transformation Services object within the Enterprise Manager tool. If you've saved your settings from a wizard run, the name you used will be displayed here. You can also create the packages from scratch by right-clicking the Local Packages object and selecting New Package from the menu item that appears.

Connections

Connections are the definitions of the sources and destinations of data. They're usually the first things you set up. Various icons within the tool define the connections you can make, or you can select these connections from the menu. You can connect to another SQL Server database, a text file, or an Oracle system.

Tasks

Tasks are commands to transfer data, edit data, send mail, run SQL scripts, run other commands, and much more. For instance, in a package you could define a SQL Server connection and a text file connection, use that to set up a transfer of data from one to the other (more on that in a minute), send email to someone, and then run a Visual Basic script. Tasks are the workhorses of the package.

Transformations

Even if you're using this tool just to transfer data unchanged from one place to another, you create a Transformation task to perform the transfer. This task actually uses something called a data pump, which is one of the engines that DTS uses to move data.

If you want more than just a straight data-for-data transfer, there are many transformation methods to choose from. You can change case or datatypes, perform text operations, and more.

Workflow

Workflows are constraints that tie tasks together. The workflow constraint has three types: success, failure, and completion. They take the result of the step they are tied to and perform the action on the other end based on whether the first task succeeds, fails, or if it just completes. You can tie all three to one task — creating a path that has some conditional logic.

For instance, our email task from the Tasks section above can be dependent on the success constraint when the transfer of data is successful. We could create a failure constraint to take a different action (such as sending an e-mail to a different person) if the data transfer fails.

More information

You can also write programs in Visual Basic, Visual C++, or C# that control DTS. Look up the subject "Building SQL Server Applications," then "DTS Programming" in the "Contents" section of Books Online for information on writing these types of programs.

This introduction is just that — an introduction. There are many uses for this tool, and they don't even have to include SQL Server. You can program and further automate DTS, as I've mentioned. Take a look at some of the tutorials and other references below to get started.

InformIT Articles and Sample Chapters

Lots of people have put a lot of time into DTS. There’s a great reference book on the subject here.

Online Resources

Euan Garden from Microsoft has a fantastic deep-dive on DTS here.