DTS Import/Export Wizard
Of all the Data Transformation Services (DTS) tools, the DTS Import/Export Wizard provides the simplest method of copying data between OLE DB data sources.
After connecting to the source and destination, you can select the data to import or export and apply transformations to the data being copied (for example, by selecting columns or using Microsoft® ActiveX® scripts). In many cases, you can automatically copy primary and foreign key constraints along with the source data.
Note You can copy data that results from an SQL query. SQL queries can include joins of multiple tables from the same database or distributed queries. As part of the process, the DTS Import/Export Wizard creates the destination table for you automatically if none exists.
Available Data Sources
With the DTS Import/Export Wizard, you can connect to the following data sources:
- Most OLE DB and ODBC data sources, as well as user-specified OLE DB data sources.
- Text files.
- Other connections to one or more instances of Microsoft SQL Server™.
- Oracle and Informix databases.
You must have the Oracle or Informix client software installed.
- Microsoft Excel spreadsheets.
- Microsoft Access and Microsoft FoxPro® databases.
- dBase or Paradox databases.
For more information, see DTS Connections.
Transforming Data
In addition to copying data, you can transform column-level data with an ActiveX scripting language such as Microsoft Visual Basic® Scripting Edition (VBScript) or Microsoft JScript®. For more information, see DTS Transformations, Transform Data Task, and Using ActiveX Scripts in DTS.
Copying Database Objects
With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task.
Saving DTS Packages
After you complete the DTS Import/Export Wizard, you can save the connections, transformations, and scheduling information as a DTS package. The package can be saved:
- To the SQL Server msdb database.
- To SQL Server 2000 Meta Data Services.
- As a structured storage file (.dts file).
- As a Visual Basic file.
You can run the package immediately or schedule it for later execution.
For more information, see Saving a DTS Package and Scheduling a DTS Package for Execution.
Editing Packages
If you create a package with the DTS Import/Export Wizard and then save it, you can edit it in DTS Designer. Using DTS Designer, you can customize the basic package you created in the DTS Import/Export Wizard, adding steps, tasks, transformations, event-driven logic, and configuring workflow. For more information, see DTS Designer.