Ms SQL basics: DTS Import, Export wizard

The DTS Import and Export wizard helps you create packages interactively, so that Program To import, export, and convert data between the source and target data sources. The following two examples illustrate how to use the DTS wizard. Each of the options involved is described in detail.

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

To export data using SQL Server Enterprise Manager, follow these steps:

(1) After starting SQL Server Enterprise Manager, log on to the specified server, right-click the data transformation services folder, select all tasks and export data in the pop-up menu, and open DTS export wizard, 18-1.

(2) Click "Next" to open the choose a data source dialog box, as shown in 18-2. Select a data source from the drop-down list next to the source server. In this example, select Microsoft ole db provider for SQL Server. If you use SQL Server Authentication, enter the valid user account and password for accessing the database, and select pubs from the drop-down list next to the database (Click Refresh first ).

(3) Click "Next" to open the choose a destination dialog box, as shown in 18-3. Select Microsoft ole db provider for SQL Server from the drop-down list next to destination. Select dba_pubs database from the drop-down list next to the database.

(4) Click "Next" to open the specify table copy or query dialog box, as shown in 18-4. In this case, you can specify the transmitted content to pass the data result set or even database objects of a table or a query. In this example, select copy table s from the source database.

(5) Click "Next" to open the select source table and view dialog box, as shown in 18-5. Select one or more tables or views for transmission. You can use the preview button to preview the data to be passed.

(6) If you want to define the correspondence between the columns of the source table and the target table during data conversion, click the box button in the trnsform column to open the columns mapping, transformation, and constrains dialog box, 18-6.

The meaning of each option is as follows: create destination table: Create the target table before copying data from the source table. By default, it is always assumed that the target table does not exist, if there is an error, unless the drop and recreate destination table if it exists option is selected; Delete rows in destination table: delete all rows in the target table before copying data from the source table, the constraints and indexes of the target table are retained. Of course, the prerequisite for using this option is that the target table must exist. append rows to destination table: add all source table data to the target table, data, indexes, and constraints in the target table are retained. However, the data is not necessarily appended to the end of the target table. If the target table has a clustered index, you can decide where to insert the data. Drop and recreate destination table: if the target table exists, delete all data and indexes in the target table and table before transmitting data from the source table, and then re-create the new target table. Enable identity insert: allows you to insert new values into the table's ID column.

(7) during data conversion, you can use a script language (such as JScript perscript VBScript) apply an operation to a column in the source table (multiply, divide, or divide the column into several, or combine several columns into one), and then copy the result to the target table. Select the transformations tab in the columns mapping, transformation, and constrains dialog box, as shown in 18-7.

(8) Select the constraints tab in the columns mapping, transformation, and constraints dialog boxes, as shown in 18-8.

(9) return to the select source table dialog box, and click "Next" to open the "Save, schedule and replicate package" dialog box, as shown in 18-9.

In the when option area, you can select the operation related to the package: run immediately: indicates that the package is run immediately; Create DTS package for replication indicates that the replication is performed by the publishing target; schedule DTS package for later execution: indicates that the package is saved and run at a certain planned time in the future.

In the Save option, select Save DTS package to save the package. SQL Server: stores packages in the MSDB database; SQL Server meta data service: stores the packages in the repository; structured storage file: stores the packages in the file format of the DTs com structure. It is easy to mail and distribute via the file server. Visual Basic file (10) Click "Next" to open the Save DTS package dialog box, as shown in 18-10. Enter the package name authorspackage in name to save the package to a local server or another remote server, or select an appropriate authentication method. If you select SQL Server Authentication, provide the user name and password.

(11) Click "Next" and click "finish" in the completing the DTs import/export wizard dialog box to end package creation. Actions 18-11 are shown.

Note: If you select the use a query to specify the data to transfer option in the specity table copy or query dual-pair box of STEP (4, click "Next" to open the type Sol statement dialog box, as shown in 18-12. Enter the SELECT statement in the blank box under query statement. The result integration of the query statement is all converted data. You can click the Query Builder button to activate the query creation wizard.