DTS Designer Example: Copying Northwind Data

This example demonstrates how to use Data Transformation Services (DTS) Designer to copy a Northwind database table from the source to the destination. The basic steps are:

1. Make two connections, one to the source and a second to the destination.
   
   
2. Add a Transform Data task that defines a source and destination table and the copying operation to be performed.
   
   
3. Add an Execute SQL task that checks for the existence of the destination table prior to copying the data. If the table does not exist, it is created. If the table exists, it is dropped and re-created.
   
   
4. Configure the workflow so the DTS package steps execute in the correct sequence.
   
   
5. Run the package.
   
   
6. Save the package so that it can be reused.

Connecting to the Source

Begin by creating a connection to the Northwind database on your local server.

To create a connection to Northwind in DTS Designer

Enterprise Manager

Connecting to the Destination

Usually, the source and destination databases are different, but for this example, you create a second connection to the Northwind database.

To create a second connection to the Northwind database using DTS Designer

Enterprise Manager

Copying the Northwind Categories Table

In DTS Designer, you usually use the Transform Data task to move the data from a source to a destination and to map transformations on the data. However, in this simple example, you copy data from a Northwind table to a second table.

To copy data from a Northwind table using DTS Designer

Enterprise Manager

Dropping and Re-Creating the Northwind Categories Table

Usually, you want to reuse a package. When you create and populate a destination table with new data, as in this example, you want to include a step where the package drops and re-creates the destination table prior to its being populated.

The easiest way to accomplish this is:

• Add an Execute SQL task to the package, with the SQL statements necessary to drop and re-create the destination table.
  
  
• Use a precedence constraint to place the completed Execute SQL task correctly in the workflow.

To configure an Execute SQL task to drop and re-create a destination table

Enterprise Manager

Configuring the Workflow

Next, you want to use a precedence constraint to place the completed Execute SQL task correctly in the workflow. In this package, the Execute SQL task is the first workflow step.

To configure workflow in the Execute SQL task

Enterprise Manager

Executing the Package

You have created a package consisting of two steps (the Execute SQL task and the Transform Data task). You now run the package, which will:

1. Make two connections to the Northwind database on the local server.
   
   
2. Check for the existence of the destination table. If the destination table does not exist, it is created. If it exists, it will be dropped and re-created.
   
   
3. Copy data from a table in the Northwind database to a second table.

To execute the package

• On the toolbar, click Execute.

  The Executing DTS Package dialog box appears, providing step and status information for the two steps. The Package Execution Results dialog box then appears.

  If the package did not execute successfully, click on the failed step in the Executing DTS Package dialog box for error information, and then check the properties of the objects you created to make sure the information entered is correct.

Saving the Package

Lastly, you want to save the package before exiting DTS Designer so it is available for reuse. You can select from several save options.

To save the DTS package to a SQL Server msdb table

Enterprise Manager

Summary

This example showed you how to build and run a simple package in DTS Designer. Now that you are familiar with the basics of DTS Designer, you can add DTS transformations, DTS tasks, and precedence constraints to a workflow. For more information, see DTS Package Elements.

BODY>