Data Transformation Services in SQL Server 2000 is a
powerful tool for moving
data.
DTS is an easy tool to start
using. But there is a lot to learn if you want to take full
advantage of the power, the flexibility, and the
extensibility of this data transformation tool. This book
can help you learn how to make full use of DTS.
Part I contains a variety of topics that help you get
started using DTS in SQL Server 2000. Chapter 1 gives a
quick look at DTS. Chapter 2 describes the enhancements
made to DTS in moving from SQL Server 7.0 to SQL Server
2000. Chapter 3 describes how DTS can be used to manage the
flow of data throughout an organization. Chapter 4 focuses
on the use of DTS to load a star schema in a data mart.
Part II begins with a description of DTS connections.
You can use Data Transformation Services with any data
source that has a conforming OLE DB provider or an ODBC
driver. SQL Server 2000 includes OLE DB providers for
Microsoft SQL Server, Microsoft Access, Microsoft Excel,
Oracle, DB2, Dbase 5, Paradox, HTML files, text files, and
for ODBC drivers.
There are 17 chapters in Parts II, III, and IV that
describe the DTS tasks in detail. Each chapter has a
section that describes when it is appropriate to use a
particular task. All the properties of the tasks are
described, with a description of how to set properties
through the normal interface, through a special tool called
Disconnected Edit, with the Dynamic Properties task, with
VBScript code, and from a Visual Basic application. A
procedure is included for creating each of the tasks in
Visual Basic.
There are extensive VBScript samples, showing how to use
transformation scripts, scripts in ActiveX Script tasks,
and workflow scripts. There are transformation scripts that
combine many records into one and one record into many.
There are examples for setting up loops in a DTS package.
There are scripts for documenting the connections in a
package, optimizing transformation column references,
setting up package transactions, creating the text of
e-mail messages, writing to text files, and setting DTS
properties.
Of special note is the chapter on the new Parallel Data
Pump task, a task that is used to import hierarchical
rowsets. SQL Server 2000 does not provide a user interface
for this task, but the author describes how to automate the
creation of these tasks, using the existing Transform Data
task functionality together with a utility the author has
created.
Part V discusses a variety of issues regarding DTS
packages and steps. You can learn how to use step
precedence, create transactions, use the Copy Database
Wizard and the Import/Export Wizard, manage packages from
Visual Basic and from SQL Server stored procedures, handle
errors in a package and its transformations, and integrate
DTS with Meta Data Services.
Part V also has a chapter on high performance DTS
packages, which includes tables and charts showing the
relative performance of various DTS tasks and options. In
some cases you can change DTS performance by a factor of
100 by selecting the appropriate task or the appropriate
settings for a task.
Part VI shows how to extend the power of DTS. There are
chapters on programming with the DTS object model, creating
a custom task with Visual Basic, and creating a custom
transformation with Visual C++.
Contents
- Chapter 1: A Quick Look at DTS
- Chapter 2: DTS Enhancements for SQL Server 2000
- Chapter 3: DTS and the Flow of Data Through the
Enterprise
- Chapter 4: Using DTS to Move Data into a Data Mart
- Chapter 5: DTS Connections
- Chapter 6: The Transform Data Task
- Chapter 7: Writing ActiveX Scripts for a Transform Data
Task
- Chapter 8: The Data Driven Query Task
- Chapter 9: The Multi-Phase Data Pump
- Chapter 10: The Parallel Data Pump Task
- Chapter 11: The Bulk Insert Task
- Chapter 12: The Execute SQL Task
- Chapter 13: The Copy SQL Server Objects Task
- Chapter 14: The File Transfer Protocol (FTP) Task
- Chapter 15: The Transfer Database and Other Transfer
Tasks
- Chapter 16: Writing Scripts for an ActiveX Script
Task
- Chapter 17: The Dynamic Properties Task
- Chapter 18: The Execute Package Task
- Chapter 19: The Message Queue Task
- Chapter 20: The Send Mail Task
- Chapter 21: The Analysis Services Tasks
- Chapter 22: The Execute Process Task
- Chapter 23: The DTS Package and its Properties
- Chapter 24: Steps and Precedence Constraints
- Chapter 25: Rapid Development with the Copy Database
Wizard and the DTS Import/Export Wizard
- Chapter 26: Managing Packages with Visual Basic
- Chapter 27: Handling Errors in a Package and its
Transformations
- Chapter 28: High Performance DTS Packages
- Chapter 29: Integrating DTS with Meta Data
Services
- Chapter 30: Programming with the DTS Object Model
- Chapter 31: Creating a Custom Task with VB
- Chapter 32: Creating a Custom Transformation with
VC++