Data Transformation Services Import and Export Wizards

The Data Transformation Services (DTS) import and export wizards allow the user to interactively create DTS packages that can be used to import, export, validate, and transform heterogeneous data using OLE DB and ODBC. DTS can also copy schema and data between relational databases. Using the DTS object transfer capability, you can also copy indexes, stored procedures, referential integrity constraints, and other database objects between computers running Microsoft® SQL Server™ version 7.0.

DTS checks to see if the destination table already exists and gives you the option to drop and re-create the destination table if desired. If the DTS Import or DTS Export wizard does not properly create the destination table, verify that the column mappings are correct, select a different data type mapping, or create the table manually. Then, copy the data.

Each database defines its own data types, and column and object naming conventions. DTS attempts to define the best possible data type matches between a source and destination. However, you can override DTS mappings and specify different destination data type, size, precision, and scale properties on the Column Mappings tab in the Column Mappings and Transformations dialog box if you are creating a new table or dropping an existing table and creating a new one.

DTS uses the source object’s name as a default. However, you can also add double quotation marks or brackets around table and column names that contain spaces if this is supported by your DBMS.

The DTS package created by the DTS Import and DTS Export wizards can be used to import, export, and transform data between a Microsoft SQL Server database and other data sources, including:

• ASCII fixed-field length text files
• ODBC data sources
• SQL Server databases
• Microsoft Excel spreadsheets
• Microsoft Access databases
• Microsoft FoxPro® databases
• dBase or Paradox databases
• User-specified OLE DB data sources

The DTS Import and DTS Export wizards allow the user to:

• Specify any custom settings supported by the OLE DB provider used to connect to the data source or destination.
• Copy an entire table, or the results of an SQL query, such as queries involving joins of multiple tables, or even distributed queries.
• Build a query using the Query Builder within the wizard. This allows users inexperienced with the SQL language to build queries interactively.
• Change the name, data type, size, precision, scale, and nullability of a column when copying the source to the destination (where a valid data type conversion applies).
• Specify transformation rules that govern how data is copied between columns of different data type, size, precision, scale, and nullability.
• Execute a Microsoft ActiveX® script that can modify (transform) the data when copied from the source to the destination, or perform any operation supported by the Microsoft JScript® or Microsoft Visual Basic® Scripting Edition languages.
• Transfer database objects such as users, roles, views, and stored procedures between computers running SQL Server 7.0
• Save the DTS package to the SQL Server msdb database, Microsoft Repository, or a COM-structured storage file.
• Specify that destination tables are to be used for replication.
• Schedule the DTS package for later execution.

Note DTS functionality may be limited by the capabilities of specific databases, ODBC drivers, or OLE DB providers. For more information about such limitations, see DTS Data Conversion and Transformation Considerations.

See Also