Professional SQL Server 2000 DTS (Data Transformation Service)

✍ Scribed by Mark Chaffin, Brian Knight, Todd Robinson

• Publisher: Peer Information
• Year: 2000
• Tongue: English
• Leaves: 682
• Edition: 1
• Category: Library

✦ Synopsis

One of the new outstanding features of SQL Server is Data Transformation Services (DTS). DTS can be used by both developers and database administrators (DBAs) and is attracting a lot of interest. There is a demand for information and currently no other books are available to satisfy this. DTS enables data to be moved between a wide variety of sources and destinations, as well as transforming the data along the way to suit your requirements. DTS can be used to maximum effect in data-driven applications, enabling dynamic data transfer across internet and intranet applications. This offers rapid and effective solutions to common problems such as: Automatically transferring data into data warehouses Importing and exporting data to and from other applications for analysis and reporting Moving working copies of databases between servers The capabilities of DTS are large and this book will give the reader a complete picture of how to use DTS to its fullest potential.

✦ Table of Contents

Cover......Page 1
Copyright......Page 2
Table of Contents......Page 6
How to Get the Most from this Book......Page 14
What's Covered in this Book......Page 15
Customer Support......Page 16
Overview......Page 17
The Next Generation in Database Solutions......Page 18
The DTS Package......Page 20
What's New in 2000?......Page 23
The DTS Toolbox......Page 24
Summary......Page 51
DTS Designer......Page 52
Tasks Available in SQL Server 2000......Page 53
Creating a DTS Package......Page 58
Logging DTS Activity......Page 80
Global Variables......Page 82
The Bulk Insert Task......Page 84
The Execute Process Task......Page 89
The Execute SQL Task......Page 90
The Copy SQL Server Objects Task......Page 95
The Send Mail Task......Page 96
The Transfer Error Messages Task......Page 97
The Transfer Master Stored Procedures Task......Page 98
The Transfer Logins Task......Page 99
DTS Interoperability......Page 100
Summary......Page 101
The Multiphase Data Pump......Page 102
Data Driven Query (DDQ)......Page 121
Dynamic Properties Task......Page 135
File Transfer Protocol Task......Page 136
Execute Package Task......Page 148
Message Queue Task......Page 154
Summary......Page 159
Advanced Package Options......Page 160
General Package Properties......Page 175
Transform Data Task Options......Page 178
Data Scrubbing......Page 186
Data Scrubbing......Page 195
DTS Lookup......Page 198
Common Errors......Page 208
Summary......Page 211
Heterogeneous Data......Page 212
Adding a Linked Server......Page 216
Using Linked Servers......Page 225
Linked Server Limitations......Page 236
Pre-Conversion Checklist......Page 237
Converting Data From Excel......Page 245
Converting DB2 Databases......Page 250
Single Threaded OLE DB Providers......Page 262
The Role of ActiveX Scripts in DTS......Page 263
Basic Programming Terminology in DTS......Page 267
Overview......Page 292
DTS Object Model......Page 293
Developing with the Object Model......Page 296
Using the Dynamic Properties Task......Page 316
Summary......Page 328
Global Variables......Page 329
The FileSystemObject Object......Page 332
The Package as an Object......Page 347
Other Helpful Objects Models in DTS......Page 359
Overview......Page 360
Locking down the msdb Database......Page 361
DTS Password Protection......Page 365
Auditing with the Microsoft Meta Data Services......Page 367
Backing Up Your Databases......Page 373
Summary......Page 387
DTS Errors......Page 388
Package-Level Auditing and Error Handling......Page 389
Task Object Error Handling......Page 396
Error Handling from External Applications......Page 407
Design Considerations for Optimal Error Handling......Page 410
Overview......Page 416
Why Use a Custom Task?......Page 417
The Task Object Model......Page 418
Building Your First Custom Task......Page 422
More About Custom Tasks......Page 426
How DTS Uses a Custom Task......Page 429
Building a Complex Custom Task......Page 430
Debugging a Custom Task......Page 458
Overview......Page 459
Data Warehousing Basics and Terminology......Page 460
Creating a Dimensional Data Model......Page 468
Creating a Solid ETL Design......Page 470
From Source to Stage......Page 473
From Stage to Warehouse......Page 480
Integrating Analysis Services......Page 481
Summary......Page 484
Overview......Page 485
Application Overview......Page 486
Data Services......Page 491
Business Services......Page 493
User Services......Page 510
Putting It All Together......Page 517
Overview......Page 520
The Northwind Dimensional Data Model......Page 523
The Northwind Staging Area Data Model......Page 541
Populating Default Values......Page 550
Microsoft Analysis Services......Page 555
Answering Northwind's Business Questions......Page 653
Appendix A: Microsoft Data Transformation Services Package Object Library Reference......Page 663
Objects......Page 665
Collections......Page 666
ActiveScriptTask......Page 667
BulkInsertTask......Page 668
Column......Page 669
Connection......Page 670
Connections......Page 671
Microsoft ActiveX Data Objects 2.6 Library Reference......Page 673