Creating a DTS Package with the DTS Import/Export Wizard

The Data Transformation Services (DTS) Import/Export Wizard offers the simplest method of building a DTS package, interactively guiding you through the process of copying and transforming data. Following are the basic steps for creating a package with the DTS Import/Export Wizard:

1. Specify whether you are importing or exporting data.

   You need to specify whether you are exporting data from an instance of Microsoft® SQL Server™ to another data source (for example, a second instance of SQL Server 2000) or importing data from another data source to an instance of SQL Server. Both choices are available in SQL Server Enterprise Manager, through the Data Transformation Services node of the console tree, and as command switches through the dtswiz command prompt utility.

   When accessing the DTS Import/Export Wizard from the Start menu or the command prompt, you do not need to specify whether you are importing or exporting data.

2. Choose a data source and data destination.

   You can select from a list of OLE DB data sources, which includes providers for both databases and nondatabase sources (for example, text files). You also must specify any required login, security, or file location information. If you are importing data from a text file, you must specify the format and delimiters of the text file.

   When you import data, the active server connection is specified as the default destination server. When you export data, the active server connection is specified as the default source server.

3. Choose whether to copy a table or view, copy query results, or transfer objects and data.
   • If you choose to copy data, you need to decide which columns or views to copy and whether to transform the data. If your source data is a view, the DTS Import/Export Wizard automatically converts the view to a table in the destination.
     
     
   • If you choose to query the source data and copy the results, you need to construct an SQL query, which also can be a heterogeneous or distributed query. You can enter the SQL query manually or graphically. After you have completed the query, you can decide whether to add transformations to the query results. For more information about graphically entering an SQL query, see DTS Query Designer.
     
     
   • If you choose to transfer database objects between instances of SQL Server, you need to: select which objects to transfer (for example, views, stored procedures, indexes, and rules); choose whether to drop existing database objects first; replace or append existing data; and include dependent objects.

     For more information, see Copy SQL Server Objects Task.

4. Optionally select columns, add transformations, or copy constraints.

   If you copy the data or the results of a query, you can customize the data being copied to the destination. You can:

   • Select which source or destination columns to copy.
     
     
   • Select which source or destination columns to ignore.
     
     
   • Change the data type where valid.
     
     
   • Define how the data is to be converted between source and destination.
     
     
   • Map transformations onto column or row data using Microsoft ActiveX® scripts.
5. Save, run, or schedule a package.

   After you have created the package, you need to decide:

   • The format in which to save the DTS package.

     You can save the package to the SQL Server msdb database, to SQL Server 2000 Meta Data Services, as a structured storage file (.dts file), or as a Microsoft Visual Basic® file.

   • When you want to run the package.

     You can run the package after the DTS Import/Export Wizard completes, or you can schedule the package to execute on a regular basis using SQL Server Agent.

     You can use the Create Publication Wizard to publish the data. For more information, see Replication Wizards.

     Important  Do not open a Microsoft Excel file that is being used as a source or destination during the wizard creation or execution, because a "file in use" error will occur.

To create a DTS package using the DTS Import/Export Wizard