| 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:
- You can specify any OLE DB settings needed 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
- Use the Query Builder to build a query specifying the data to export
- Define destination options by including the name, data type, size, precision, scale, and nullability of a column
- Create tables in the destination data source
- Specify rules on how to transform data, including how to transform data types, size, precision, scale, and nullability
- 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
- Transfer database objects such as users, roles, views, and stored procedures between SQL Server 7.0 database servers
Save the DTS package to the SQL Server msdb database, Microsoft Repository, or a COM -structured storage file
Configure SQL Server destination tables for replication
Schedule the DTS package execution
- msdb: The system database that is used to store SQL Server Agent information and DTS information.
- 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.
- COM: Component Object Model is a model for APIs used to access data.
In the next lesson, running the DTS Wizard will be discussed.