Data Management

Transferring data with SQL Server 2000

Transferring data between SQL Server and other systems is a common task. Microsoft provides various tools to fill this need, but DTS is the most user-friendly.

Microsoft SQL Server 2000 provides the Data Transfer Service (DTS) tool to streamline the task of moving data between disparate systems. It's available as a tool in the SQL Server Enterprise Manager client interface. Let’s take a closer look at how to access and use DTS. For my example, I will export data from SQL Server to an Excel spreadsheet.

Moving data out of SQL Server
You can access DTS from Enterprise Manager and use it to import and export data to and from SQL Server (Figure A). Each process is straightforward; dialog boxes guide you through the entire process.

Using DTS, you can exchange data with a variety of data stores including Paradox, Microsoft Excel and Access, dBase, FoxPro, and text files.

Figure A
Accessing DTS from Enterprise Manager

For example, to export data to an Excel spreadsheet from SQL Server, you would first create an Excel spreadsheet to receive the exported data. The spreadsheet requires no extra setup; you just need the container (spreadsheet) for the exported data. The DTS export process creates the appropriate Excel sheets within the spreadsheet to receive the data. The selection of DTS initiates a series of dialog boxes that guide you through the process.

Figure B shows the dialog box that asks you to select the data source from which the data is exported. The Data Source drop-down list allows you to choose the type of data source (e.g., SQL Server, Access, Paradox, text file, etc.). The options you select in the areas below the Data Source field depend upon which data source you select.

Figure B
Initiating a DTS export process

In Figure B, I chose SQL Server as the data source. I specified the server name/address, the login, and whether Windows or SQL Server authentication is utilized. The username and password are entered for the SQL Server login, and the source database is selected from a drop-down list.

After clicking Next, you will see a screen that allows you to specify the destination data source. The destination dialog box coincides with the data source selection from Figure B. Data may be transported to a variety of formats. For this example, I chose Microsoft Excel, and I selected the previously created filename as the destination.

Then, after you click Next, you'll see the screen in Figure C. From this screen, you can specify whether tables and views are copied from the data source, or you can enter a SQL query to select records.

Figure C
Choosing the data to export

If you select Copy Table(s) And View(s) From The Source Database, you will be presented with the screen in Figure D. The Transform column allows the export options for the data to be edited. From this screen, you also choose the tables and views to be exported, and you can edit the destination name.

Figure D
Choosing the tables/views to export

Choosing the option Use A Query To Specify The Data To Transfer from the screen in Figure C will open the dialog box shown in Figure E.

Figure E
Exporting data via SQL query

The next dialog box (Figure F) allows you to run the process immediately, schedule it, or save the DTS package for later. After selecting your option and clicking Next, the following dialog box allows you to review the process and run it (by clicking the Finish button).

Figure F
Choosing when the process executes

Figure G
Export results

The final dialog box displays the results of the process (whether or not it was successful) as shown in Figure G. The dialog boxes may seem overwhelming, but the process moves quickly. I chose two tables, and the resulting Excel spreadsheet is displayed in Figure H.

Figure H
Exported data presented in Excel

Notice that the exported data in Figure H is divided into sheets within the Excel worksheet. Each sheet corresponds to a table (or view) exported from the data source. The data can then be easily utilized for reporting or used for calculations within Excel.

Reversing the process
The process of importing the data follows the same path as the export process. Data may be imported into SQL Server from a variety or sources (as I mentioned). Remember to take great care during the import process, because DTS has a hard time identifying field and table names and it often assigns default names. However, DTS does provide a way to edit the import assignments to ensure the process is smooth, but this is beyond the scope of this article.

Other options
DTS is just one data transport option provided by SQL Server 2000. The command line bulk copy program (bcp) is still available as well as the SQL bulk insert command. There are various approaches to working with data both inside and outside the SQL Server 2000 environment, but DTS is the most user-friendly. Microsoft Online Books provide any additional information necessary to fully utilize DTS in your projects.