Running the DTS Wizard

The DTS Wizard is a streamlined interface solely to generate DTS packages for importing or exporting of data. However, you will find that it is really quite powerful and provides an easy but sophisticated way to move data from or to any OLE DB, ODBC, or text source to another OLE DB, ODBC, or text source. You can also define simple or complex data transformations using the many options provided by the wizard or, for more complex transformations, using an ActiveX script written in VBScript or JScript from within the wizard. You can also copy database schema, but the transfer of all other database objects, such as indexes, constraints, users, permissions, stored procedures, and so on, is only supported between SQL Server 7.0 and above SQL Servers.

The DTS Wizard (whether an Export or Import is selected) takes the user through five basic steps:

1. Select/identify the datasource.

2. Select/identify the destination (target).

3. Select the data copy and transformation type. The options are to copy data with or without the schema, to move data based on a query, or to transfer objects and data between SQL 7.0 (or above) servers.

4. Define any data transformations, if required.

5. Save, schedule, and execute the package.

Now follow along with the DTS Wizard steps.

1. Fire up the DTS Wizard from the Microsoft SQL Server Program Group or from within Enterprise Manager, Tools (see Figure 20.17).

   Figure 20.17. Two methods of invoking DTS Wizard.

   

2. After the DTS Wizard has been initiated, it will walk you through the steps previously outlined. The initial step is identifying the source and target (destination) data connections. In addition, there is also the capability to set any advanced connection options that are needed by a specific connection requirement (see Figure 20.18).

   Figure 20.18. DTS Wizard connection options.

   

3. Next is the data and transformation type. For this, you will specify that a SQL Query will be needed to do the aggregations that you need (using SQL) at a transformation and movement (see Figure 20.19).

   Figure 20.19. Table Query specification.

   

4. You will be prompted to identify the destination information (the target table in this case) and also have a chance to preview the data that will be generated from the Query execution (see Figure 20.20).

   Figure 20.20. Source tables and Data view.

   

   Hint: When initially setting up your packages, make sure they point to a valid test environment (not production!). The connection information can be updated to point to the right place after you have completely debugged the DTS package.

5. The next couple of wizard prompts take you through the Column selection process for the destination (target). You will also be prompted for the additional things you might need to do on the destination table side (that is, delete the rows first, create the destination table, or just append rows in the destination table). You can now see the column mappings from source to destination (see Figure 20.21).

   Figure 20.21. Column selection and mappings.

   

6. Figure 20.22 indicates that you are now ready to save the DTS Package (which can be password protected) and asks what you would like to do next:

   • Run immediately.

   • Schedule DTS package for later execution.

   • Save DTS package. You can save it in a SQL Server (MSDB), SQL Server Metadata Services (recommended), in a structured storage file, or in a Visual Basic file (see Figure 20.22).

   Figure 20.22. Naming and saving DTS Packages.

   

7. Last, the DTS Wizard will display its summary dialog box and ask you to finish. After you select Finish, the DTS Package will be executed because Run Immediately was selected. You will see the Executing Package dialog box with each major task listed along with the progress and status of each task (see Figure 20.23).

   Figure 20.23. DTS Wizard summary and execution.

   

Next, you will look at the DTS Designer GUI and its visual manipulation capabilities.