Importing/Exporting Data  «Prev  Next»

Lesson 9 Data Transformation Service Wizard
Objective Identify key features of DTS Wizard.

Data Transformation Service Wizard in SQL Server

The DTS Wizard provides the capability to import and export data between two data sources.
The DTS Designer can perform multiple steps in a transformation, send data to multiple data sources, and receive data from multiple data sources. However, the DTS Wizard can only do single-step transformations, sending data from one source and receiving data from one source.

DTS Wizard features

The DTS Wizard has the following features:
  1. You can specify any OLE DB settings needed to connect to the data source or destination
  2. Copy an entire table, or the results of an SQL query, such as queries involving joins of multiple tables, or even distributed queries
  3. Use the Query Builder to build a query specifying the data to export
  4. Define destination options by including the name, data type, size, precision, scale, and nullability of a column
  5. Create tables in the destination data source
  6. Specify rules on how to transform data, including how to transform data types, size, precision, scale, and nullability
  7. Execute a Microsoft ActiveX® script ,Microsoft JScript® or Microsoft Visual Basic® Script that specifies how to transformthe data when copied from the source to the destination
  8. Transfer database objects such as users, roles, views, and stored procedures between SQL Server 7.0 database servers
  9. Save the DTS package to the SQL Server msdb database, Microsoft Repository, or a COM -structured storage file
  10. Configure SQL Server destination tables for replication
  11. Schedule the DTS package execution

  1. msdb: The system database that is used to store SQL Server Agent information and DTS information.
  2. Microsoft Repository: A set of Microsoft ActiveX interfaces and data models that are used to define database schema and data transformations as specified by the Microsoft Data Warehousing Framework.
  3. COM: Component Object Model is a model for APIs used to access data.
In the next lesson, running the DTS Wizard will be discussed.