Using Data Transformation Services - Part 1
(Page 1 of 5 )
Data Transformation Services (DTS) enable transfer of data and objects between different SQL 2000 Server systems. This article includes an overview of the DTS package, as well as step-by-step instructions for transferring a table from the SQL Server to an MS Access file.
SQL-DMF: SQL Distributed Management Framework is a set of utilities that are not part of the database engine (like Query Analyzer and Enterprise Manager). There are three application programming interfaces, or utilities that allow us to access all the objects in the SQL Server. They are:
- SQL Namespace - SQL-NS
- SQL Distributed Management Objects - SQL-DMO
- SQL Data Transformation Services - SQL-DTS
DTS, which stands for Data Transformation Services, enable transfer of data and objects between different SQL 2000 Server systems. DTS was originally created to transfer data from OLTP data into OLAP data, which included the various steps of extracting the data, cleansing and transforming to remove inconsistencies, and then loading the data (popularly known as ETL). DTS can validate and summarize data while transferring -- making it extremely powerful.
DTS is available to all SQL Server editions from Enterprise to MSDE. DTS uses OLEDB, so it can be used for transferring data from one OLEDB to another OLEDB and therefore is not specific to SQL Server. It could be used to transfer data between any OLEDB supported databases, for example between Oracle to MS Access without an SQL 2000 middle man. Since Microsoft OLEDB for ODBC can connect to ODBC sources, the data transfer functionality is extended to ODBC sources, and DTS assumes an even more powerful role.
The DTS Data Pump is at the heart of the transfer process connecting from source to sink. While data is being transferred, it may also be transformed by using the ActiveX scripting languages like jscript and vbscript. This special feature operates on rows of data where column contents from the source side can be made to match up with what is required by the destination, or even more demanding complex functions, as well as validated retrieval from external sources to local source can be accomplished.
Contents of this tutorial
There are two tools and an API available in SQL 2000 Server to interactively create DTS Packages. These are:
- Import Export Wizard
- DTS Designer
- DTS API
The first part of this tutorial shows a step-by-step approach for transferring a table from the SQL Server to an MS Access file. In the second part, an overview of the DTS package is presented. This is followed by the presentation of a concrete step-by-step example of copying a database from an SQL Entrprise Server to an MSDE server in the same machine. A future tutorial will consider the DTS API in considerable detail. While the Import Export Wizard is quite powerful, using the DTS Editor will provide a lot more power and flexibility to fine tune the data transfer.
Next: Import Export Wizard >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 35 
