Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 26, 1998

Tools (Cont'd) - Page 6

By Sharon Dooley

25 Jan 98

Data Transformation Services (DTS)

In many ways, this was the coolest thing I saw at the briefing. It was hidden away in the data warehousing presentation but has uses far beyond that. I was totally unclear about this features function from a limited scan of the beta documentation.


The purpose of DTS is to make it easier to import, export and transform heterogeneous data. It provides for data lineage if desired. It supports transformations between the source and target data sources (which need not be SQL Server). These transformations include such things as

Data quality checks and validation; supplying missing values, scrubbing data

Data integration and duplicate elimination

Combine multiple columns into one

Build several rows from one input


It has the concept of a package, which is a series of tasks to be performed as a part of a transformation. This provides a simple "workflow".

It has its own engine (In process COM Server) and can be used independent of SQL Server.

It allows you to write scripts (for each column) in VBScript, and JavaScript. Perl is supposed to be added as a scripting language.

The demo I saw quickly moved a database from Oracle 8 and an Excel spreadsheet to SQL Server.


This is clearly a super tool for conversion and massaging of data.


Microsoft has made a distinction between the Query Processor ("Relational Engine") and the Storage Engine. They are distinct subsystems. The overall design of both is controlled by Gertz Grefer (I am not sure of the spelling). He comes from Tandem. He did not present but did participate in the final panel discussion.

Client processes communicate with the Relational Engine via DBLIB (old, not being enhanced), ODBC 3.6 and OLE DB. SQL Server still receives client requests via TDS. DBLIB clients communicate with TDS 4.2/6, ODBC and OLE DB clients use TDS 7. The relational engine communicates with the storage engine via OLE DB.

Query Processor

The query processor (QP) has been completely redesigned. Goals are to provide excellent decision support performance, support for very large databases, and to support heterogeneous query and update. The following picture illustrates the ultimate goal. Note that the SQL is pseudo code. Initially there will only be support for SQL Server as a database engine.


Many thanks to Sharon for providing these notes - drop her a note at sharond@compuserve.com and tell her thanks!


MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM