Chapter 1: Jumping into DTS

Overview

In this chapter, you will learn how to:

• Import data into SQL Server using the DTS Import/Export Wizard

• Export data from SQL Server using the DTS Import/Export Wizard

• Copy objects and data between SQL Server databases using the DTS Import/ Export Wizard

One of the challenges business executives face today is the integration of data from disparate sources and formats into a single data store to assist them in making informed business decisions. To populate this integrated data store, the data movement application developer needs a single tool that is flexible and extensible enough to meet the needs of experienced developers, yet simple enough for beginning developers to use. In addition, the data movement application built by the tool must be easy for Database Administrators (DBAs) to manage and control.

Microsoft SQL Server 2000 Data Transformation Services (DTS) is this tool. You can use DTS to create simple data movement applications by using the DTS Import/Export Wizard, which we ll discuss in this chapter. You can create more complex data movement applications by using DTS Designer, which you ll learn about in Chapter 2, or by using Microsoft Visual Basic. The data movement application can range in complexity from a single DTS package to a large set of interrelated DTS packages. Each DTS package contains connection objects, tasks , and precedence constraints. Connection objects define data sources, data destinations, and data lookup structures. Tasks define the actions that will be performed within the DTS package, and they use the connection objects as needed. Precedence constraints control the order in which package tasks are executed. Tasks without precedence constraints execute in parallel when the package is executed.

The DTS Import/Export Wizard enables you to quickly copy all data or a subset of data from a data source to a data destination, optionally performing a variety of data transformations during the process. If the data source and data destination are SQL Server databases, you can also copy database objects (such as logins and indexes) from the data source to the data destination. The wizard enables you to create a one-time project or save the project steps into a package that can be executed at a later time. Saved packages can also be modified and extended using DTS Designer or Visual Basic.

This chapter focuses on the DTS Import/Export Wizard because it is the quickest and easiest way to expose beginners to the capabilities of DTS. If you are already familiar with the capabilities of this wizard, you can skip ahead to Chapter 2.