MS SQL Basics Tutorial: DTS Import, Export Wizard

The DTS Import, Export Wizard helps users create packages interactively to import, export, and transform data between source and target data sources with OLE DB and ODBC drivers. Here are two concrete examples of how to use the DTS Wizard, with a detailed description of each of the options involved.

Example 18-1: Use the DTS Wizard to export the authors table in the pubs database.

The steps to perform data export using SQL Server Enterprise Manager are:

(1) After starting SQL Server Enterprise Manager, log on to the specified server, right-click the Data Transformation Services folder, select All Tasks in the pop-up menu, Export data, open DTS Ex Port Wizard, as shown in Figure 18-1.

(2) Click the Next button to open the Choose a Data Source dialog box, as shown in Figure 18-2. First, select the data source in the Drop-down list next to the source Server. In this example, select Microsoft OLE DB Provider for SQL Server. If you are using SQL Server authentication, you should enter a legal user account and password to access the database, and select pubs (click the Refresh button) in the Drop-down list next to the databases.

(3) Click the Next button to open the Choose a Destination dialog box, as shown in Figure 18-3. Select Microsoft OLE DB Provider for SQL Server in the Drop-down list next to destination. Select the Dba_pubs database in the Drop-down list next to the databases.

(4) Click the Next button to open the Specify Table Copy or Query dialog box, as shown in Figure 18-4. Here you can specify what to pass, and you can pass a table or a query's data result set, or even a database object. In this example, select the Copy table s from the source database.