Microsoft SQL Server 2000 Data Transformation Services- P1

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

80
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Microsoft SQL Server 2000 Data Transformation Services- P1: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for moving data. It’s easy to use, especially when you’re creating transformation packages with the DTS Wizard. But it’s also a flexible tool that you can customize to gain a high degree of control over the transformation of your data.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Microsoft SQL Server 2000 Data Transformation Services- P1

Please purchase PDF Split-Merge on www.verypdf.com to remo
Microsoft SQL Server 2000   Data Transformation Services Timothy Peterson 201 West 103rd St., Indianapolis, Indiana, 46290 USA
ACQUISITIONS EDITOR Microsoft SQL Server 2000 Sharon Cox Data Transformation Services DEVELOPMENT EDITOR Copyright © 2001 by Sams Publishing Kevin Howard All rights reserved. No part of this book shall be reproduced, stored in a EXECUTIVE EDITOR retrieval system, or transmitted by any means, electronic, mechanical, photo- Rosemarie Graham copying, recording, or otherwise, without written permission from the pub- lisher. No patent liability is assumed with respect to the use of the information MANAGING EDITOR contained herein. Although every precaution has been taken in the preparation Charlotte Clapp of this book, the publisher and author assume no responsibility for errors or PROJECT EDITOR omissions. Nor is any liability assumed for damages resulting from the use of Elizabeth Finney the information contained herein. International Standard Book Number: 0-672-32011-8 COPY EDITOR Sean Medlock Library of Congress Catalog Card Number: 00-103543 INDEXER Printed in the United States of America Tina Trettin First Printing: December 2000 PROOFREADER 03 02 01 00 4 3 2 1 Tony Reitz TECHNICAL EDITORS Trademarks Todd Meister All terms mentioned in this book that are known to be trademarks or service Ivan Oss marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be TEAM COORDINATOR regarded as affecting the validity of any trademark or service mark. Pamalee Nelson Microsoft is a registered trademark of Microsoft Corporation. MEDIA DEVELOPER JG Moore SQL Server is a trademark of Microsoft Corporation. INTERIOR DESIGNER Warning and Disclaimer Anne Jones Every effort has been made to make this book as complete and as accurate as COVER DESIGNER possible, but no warranty or fitness is implied. The information provided is on Anne Jones an “as is” basis. The author and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages aris- ing from the information contained in this book or from the use of programs accompanying it.
Overview PART I Getting Started with DTS 1 A Quick Look at DTS 5 2 DTS Enhancements for SQL Server 2000 41 3 DTS and the Flow of Data Through the Enterprise 59 4 Using DTS to Move Data into a Data Mart 77 PART II DTS Connections and the Data Transformation Tasks 5 DTS Connections 101 6 The Transform Data Task 125 7 Writing ActiveX Scripts for a Transform Data Task 179 8 The Data Driven Query Task 213 9 The Multiphase Data Pump 227 10 The Parallel Data Pump Task 247 PART III Other Data Movement and Manipulation Tasks 11 The Bulk Insert Task 267 12 The Execute SQL Task 291 13 The Copy SQL Server Objects Tasks 309 14 The File Transfer Protocol (FTP) Task 325 15 The Transfer Database and Other Transfer Tasks 333 PART IV Control Tasks 16 Writing Scripts for an ActiveX Script Task 345 17 The Dynamic Properties Task 365 18 The Execute Package Task 377 19 The Message Queue Task 391 20 The Send Mail Task 405 21 The Analysis Services Tasks 411 22 The Execute Process Task 425
PART V DTS Packages and Steps 23 The DTS Package and Its Properties 435 24 Steps and Precedence Constraints 469 25 Rapid Development with the Copy Database Wizard and the DTS Import/Export Wizard 501 26 Managing Packages with Visual Basic and Stored Procedures 525 27 Handling Errors in a Package and Its Transformations 553 28 High-Performance DTS Packages 565 29 Integrating DTS with Meta Data Services 587 PART VI Extending the Power of DTS 30 Programming with the DTS Object Model 611 31 Creating a Custom Task in VB 633 32 Creating a Custom Transformation with VC++ 659 Index 679
Contents Introduction 1 Using DTS in SQL Server 7.0 and SQL Server 2000 ............................1 The Code Samples ..................................................................................1 Keeping Current with Information on DTS ............................................2 PART I Getting Started with DTS 1 A Quick Look at DTS 5 A High-Performance Data Pump—and a Whole Lot More ....................6 Using the DTS Designer to Create Complex Data Transformations ......7 The Structure of a DTS Package........................................................8 Creating Connections ......................................................................10 Creating Tasks ..................................................................................11 Setting the Workflow........................................................................12 Saving and Retrieving DTS Packages ..................................................14 SQL Server Storage..........................................................................14 SQL Server Meta Data Services Storage ........................................15 Structured Storage File ....................................................................15 Visual Basic File ..............................................................................15 Package Versions ..............................................................................16 How to Execute a DTS Package............................................................16 From the DTS Designer ..................................................................16 From the Wizards ............................................................................17 From the Enterprise Manager ..........................................................17 From Visual Basic Code ..................................................................18 With the OLE Automation System Stored Procedures ....................18 As a Data Provider ..........................................................................19 Using the DTSRun Command Prompt Utility ................................19 Using the DTSRun Utility for Windows ..........................................22 From Another DTS Package ............................................................23 Scheduling a DTS Package ..............................................................24 The Execution Context for a DTS Package ....................................25 Using the DTS Object Model for Programmatic Control ....................27 The DTS Object Model ....................................................................27 Using the DTS Object Model with the Dynamic Properties Task ................................................................................................28 Using the DTS Object Model with Disconnected Edit....................29 Programming DTS with VBScript ..................................................31 Programming DTS with Visual Basic ..............................................31 Programming DTS with VC++ ........................................................32
vi MICROSOFT SQL SERVER 2000 DATA TRANSFORMATION SERVICES DTS Templates ......................................................................................32 Using Templates ..............................................................................32 Creating Templates ..........................................................................32 Using Wizards for Rapid Application Development ............................33 Copy Database Wizard ....................................................................33 DTS Import/Export Wizard ..............................................................34 Practical Uses for DTS ..........................................................................36 Upgrading a Database from Microsoft Access to SQL Server ........36 Consolidating Data from Multiple Sources......................................36 Initial and Periodic Loading of a Data Mart or a Data Warehouse......................................................................................37 Reporting on Data from Transaction Processing Systems ..............38 Building an Interface to the Web for a Legacy System ..................38 Archiving a Database ......................................................................38 Analyzing Internet Clickstream Data ..............................................39 Importing and Exporting with XML ................................................40 Conclusion ............................................................................................40 2 DTS Enhancements for SQL Server 2000 41 Top Ten New DTS Features ..................................................................42 The New DTS Tasks ..............................................................................43 Dynamic Properties Task..................................................................43 The File Transfer Protocol (FTP) Task ............................................44 The Execute Package Task ..............................................................44 The Message Queue Task ................................................................44 The Parallel Data Pump Task ..........................................................44 The Analysis Services Processing Task............................................44 The Data Mining Prediction Query Task ........................................45 The Transfer Databases Task............................................................45 The Transfer Master Stored Procedures Task ..................................45 The Transfer Jobs Task ....................................................................46 The Transfer Logins Task ................................................................46 The Transfer Error Messages Task ..................................................46 Transformation Task Enhancements......................................................47 Multiple Phases in the Data Transformation Tasks..........................47 Package Object Browser When Writing Scripts ..............................47 The New Data Transformations ............................................................49 Using Parameters in the Source Query ............................................49 New Error File Options ....................................................................49 Lookups Can Now Modify Data ......................................................50 New Features in Other Tasks ................................................................50 Using Input Parameters in the Execute SQL Task ..........................50 Assigning the Result of the Execute SQL Task to Parameters ........51 Assigning the Result of the Execute SQL Task to Recordset..........51
vii CONTENTS Package Object Browser for Writing ActiveX Scripts ....................52 Auto-Generating a Format File in the Bulk Insert Task ..................52 Other New Features in DTS ..................................................................52 Copy Database Wizard ....................................................................52 Save To Visual Basic File ................................................................53 Setting Values of the Package Global Variables with DTSRun ......53 DTSRunUI—A Windows Interface for DTSRun ............................53 Package Templates ..........................................................................54 Disconnected Edit ............................................................................54 Option Explicit for Global Variables................................................55 Just-In-Time Debugging ..................................................................55 Turn On Package Cache ..................................................................56 Executing Individual Steps in the Package Designer ......................56 DTS Package Logs ..........................................................................56 Other New Features in SQL Server 2000 That Enhance Data Transformation............................................................................56 Integration with XML ......................................................................56 Indexed Views ..................................................................................57 User-Defined Functions....................................................................57 Conclusion ............................................................................................57 3 DTS and the Flow of Data Through the Enterprise 59 Enterprise Data Structure and Data Flow ..............................................61 Business Drivers for Enterprise Data Transformation ..........................61 Ways to Use Data ..................................................................................63 Transaction Processing ....................................................................63 Business Analysis ............................................................................64 Maintaining History..........................................................................68 Sources of Data......................................................................................68 On Line Transaction Processing (OLTP) Systems ..........................68 Web Clickstream Data......................................................................69 Data from Outside the Organization ................................................69 The Data Warehouse ........................................................................69 Results from Business Analysis Systems ........................................69 Meta Data ..............................................................................................70 Types of Data Storage............................................................................71 Text Files ..........................................................................................72 XML ................................................................................................72 Spreadsheets ....................................................................................74 Relational Database Management Systems......................................74 Multidimensional Database Management Systems (OLAP) ..........76 Conclusion ............................................................................................76
viii MICROSOFT SQL SERVER 2000 DATA TRANSFORMATION SERVICES 4 Using DTS to Move Data into a Data Mart 77 Multidimensional Data Modeling..........................................................78 Differences Between Relational Modeling and Multidimensional Modeling ..........................................................80 The Fact Table........................................................................................82 Choosing the Measures ....................................................................83 Choosing the Level of Summarization for the Measures ................83 The Dimension Tables ..........................................................................84 The Primary Key in a Dimension Table ..........................................85 Levels of the Dimension Hierarchy..................................................86 Attributes of the Dimension ............................................................86 The Time Dimension ........................................................................87 Subordinate Dimension Keys ..........................................................88 Loading the Star Schema ......................................................................88 Loading Data into a Staging Area ....................................................89 Loading the Dimension Tables ........................................................90 Updating the Subordinate Dimension Keys ....................................92 Loading the Fact Table ....................................................................92 Using the Data ..................................................................................93 Avoiding Updates to Dimension Tables ................................................94 Conclusion ............................................................................................98 PART II DTS Connections and the Data Transformation Tasks 5 DTS Connections 101 Creating DTS Connections ..................................................................103 DTS Connection Properties ................................................................104 Properties That Identify a DTS Connection ..................................104 Properties That Determine Access to the Data Source ..................108 Other Properties..............................................................................112 Creating Connections for Different OLE DB Providers ....................114 The SQL Server OLE DB Provider ..............................................114 Text File Connections ....................................................................115 The OLE DB Provider for OLAP Services....................................119 Other OLE DB Providers ..............................................................119 Modifying Data Connections Dynamically ........................................121 Performance Issues with DTS Connections ........................................122 Conclusion ..........................................................................................123 6 The Transform Data Task 125 When to Use the Transform Data Task................................................126 Creating a New Transform Data Task ................................................128 Using the Package Designer ..........................................................128 Using the DTS Import/Export Wizard ..........................................129 Using Code ....................................................................................129
ix CONTENTS The Description and Name of the Task ..............................................129 The Source of a Transform Data Task ................................................130 Text File Source..............................................................................131 SQL Table, View, or Query for a Relational Database Source ......131 MDX Query for a Multidimensional Cube Source........................133 Using XML as the Source ..............................................................134 Using Parameters in a Source Query ............................................135 DataPumpTask Source Properties ..................................................136 The Destination of a Transform Data Task..........................................137 Creating a New Destination Table..................................................137 Text File Destination ......................................................................138 DataPumpTask Destination Properties ............................................139 Mapping Source Columns to Destination Columns ............................139 Transformation Flags......................................................................144 Testing a Transformation................................................................146 The Collections That Implement a Transformation ......................146 Other Properties of a Transformation ............................................147 The Transformation Types ..................................................................148 The DateTime String ......................................................................148 Uppercase Strings, Lowercase Strings, and Copy Column............149 Middle of String and Trim String ..................................................149 Read File ........................................................................................151 Write File........................................................................................151 ActiveX Script ................................................................................152 Custom Transformation ..................................................................152 Other Properties of the Transform Data Task......................................153 Error Handling Properties ..............................................................154 Data Movement Properties ............................................................156 Options for Improving Performance with SQL Server Destinations ......................................................................157 Column Properties ..............................................................................159 Creating a Transform Data Task in Visual Basic ................................160 Creating Transformations in Code ......................................................165 Choosing a Transformation Type in Code......................................165 Creating the Transformation and Its Columns ..............................167 Copy Column, Uppercase, and Lowercase Transformations ........169 Trim String and Middle of String Transformations ......................169 Read File and Write File Transformations ....................................170 DateTime Transformations ............................................................171 ActiveX Transformations ..............................................................172 A Sample Application with All the Transformations ....................172 Using the Transform Data Task as a FreeStanding Icon ....................173 Conclusion ..........................................................................................177
x MICROSOFT SQL SERVER 2000 DATA TRANSFORMATION SERVICES 7 Writing ActiveX Scripts for a Transform Data Task 179 When You Should Use the ActiveX Script Transformation ................180 Deciding Between One Task and Many ........................................181 Using the Variety of Transformation Types ..................................181 Transformation ActiveX Scripts Basics ..............................................182 The Transformation ActiveX Script Development Environment ........183 Choosing a Scripting Language ..........................................................187 Setting the DTS Transformation Status ..............................................188 DTSTransformStat_OK ....................................................................188 DTSTransformStat_SkipRow ..........................................................188 DTSTransformStat_SkipFetch ......................................................189 DTSTransformStat_SkipInsert ......................................................189 DTSTransformStat_DestDataNotSet ..............................................189 DTSTransformStat_Info ................................................................190 DTSTransformStat_OKInfo ............................................................190 DTSTransformStat_SkipRowInfo ....................................................190 DTSTransformStat_Error ..............................................................190 DTSTransformStat_ErrorSkipRow ..................................................191 DTSTransformStat_ExceptionRow ..................................................191 DTSTransformStat_AbortPump ......................................................191 DTSTransformStat_NoMoreRows ......................................................191 Creating and Using Local Variables ....................................................192 Variable Types ................................................................................192 Object Variables..............................................................................193 Using Option Explicit ....................................................................194 Creating and Using Global Variables ..................................................194 Creating Global Variables in the User Interface ............................194 Creating Global Variables in an ActiveX Script ............................196 Case Sensitivity of Global Variables and Option Explicit ............197 The Lock and Unlock Methods of the GlobalVariable2 Object ..........................................................................................198 Creating and Using Lookups ..............................................................198 Creating Lookups with the User Interface ....................................199 Creating Lookup Objects in an ActiveX Script..............................200 Using a Lookup in an ActiveX Script ............................................200 Using a Lookup to Modify Data ....................................................201 Using ActiveX Scripts or Modifying the Source Query......................202 Simple Assignment of Fields..........................................................202 String Manipulation........................................................................202 Handling Unknown Values ............................................................203 Looking Up an Unknown Value ....................................................204 Using an Outer Join to Protect Against Missing Data ..................205 Merging Data from Two Sources with a Full Outer Join ..............206
xi CONTENTS Separating Information from One Record into Several Records ........206 Combining Information from Several Records into One ....................210 Conclusion ..........................................................................................212 8 The Data Driven Query Task 213 When to Use the Data Driven Query Task ..........................................215 Creating a Data Driven Query Task ....................................................216 Transformation Status Constants for the Data Driven Query Task ........................................................................................219 A Data Driven Query Example............................................................220 Creating a Data Driven Query Task in Visual Basic ..........................223 Conclusion ..........................................................................................226 9 The Multiphase Data Pump 227 Enabling the Multiphase Data Pump ..................................................228 Programmatic Flow with Multiple Phases ..........................................230 Using the Phases ..................................................................................233 Pre Source Phase ............................................................................235 Row Transform Phase ....................................................................237 Post Row Transform Phase ............................................................237 On Transform Failure Subphase ....................................................238 On Insert Failure Subphase ............................................................238 On Insert Success Subphase ..........................................................239 On Batch Complete Phase..............................................................240 Post Source Data Phase ..................................................................241 Pump Complete Phase....................................................................242 Creating a COM Object with Visual C++ to Program the Phases ..........................................................................................243 Creating a Multiphase Data Pump in Code ........................................243 The TransformPhases Property......................................................243 Setting the Entrance Functions ......................................................244 Conclusion ..........................................................................................245 10 The Parallel Data Pump Task 247 Hierarchical Rowsets ..........................................................................249 When to Use the Parallel Data Pump Task..........................................251 The Collections and the Properties of the Parallel Data Pump Task ........................................................................................252 The Transformation Modes..................................................................252 Hierarchical Mode ..........................................................................253 Flattened Mode ..............................................................................253 Data Driven Query Mode ..............................................................253 The TransformationSetOptions Property ....................................253 Creating a Parallel Data Pump Task in Visual Basic ..........................254 A User Interface for the Parallel Data Pump Task ..............................257 Conclusion ..........................................................................................263
xii MICROSOFT SQL SERVER 2000 DATA TRANSFORMATION SERVICES PART III Other Data Movement and Manipulation Tasks 11 The Bulk Insert Task 267 When to Choose the Bulk Insert Task Over the Transform Data Task ..........................................................................................269 Creating a Bulk Insert Task ................................................................270 The Destination for a Bulk Insert Task................................................270 The Source for a Bulk Insert ..............................................................271 Fixed-Length Text Files..................................................................271 Delimited Text Files ......................................................................271 Format Files ........................................................................................271 Format File Structure......................................................................272 Generating a Format File................................................................274 Reconciling Differences Between the Source and the Destination ........................................................................................276 Extra Fields in the Data Destination Table ....................................277 Rearranging Fields When Moving from Source to Destination ..................................................................................278 Extra Fields in the Source Text File ..............................................279 Other Properties of the Bulk Insert Task ............................................281 Check Constraints ..........................................................................281 Keep Nulls ......................................................................................282 Enable Identity Insert ....................................................................283 Table Lock ......................................................................................283 Sorted Data ....................................................................................284 Code Page ......................................................................................284 Data File Type ................................................................................285 Insert Commit Size ........................................................................285 Maximum Errors ............................................................................286 Only Copy Selected Rows, Starting with Row, and Stopping at Row ..........................................................................286 Creating a Bulk Insert Task in Visual Basic ........................................287 Conclusion ..........................................................................................290 12 The Execute SQL Task 291 When to Use the Execute SQL Task ..................................................292 Creating the Execute SQL Task ..........................................................292 Writing Queries for Different Database Systems ................................294 Using Input Parameters in Execute SQL Tasks ..................................294 Using Output Parameters for Row Values ..........................................296 Using an Output Parameter for the Rowset ........................................299 Dynamically Modifying the SQL Statement ......................................300 Using the Execute SQL Task to Execute a DTS Package from a Remote Server ......................................................................301 Creating an Execute SQL Task in Visual Basic ..................................306 Conclusion ..........................................................................................308
xiii CONTENTS 13 The Copy SQL Server Objects Task 309 When to Use the Copy SQL Server Objects Task ..............................310 The Source and the Destination ..........................................................311 Transfer Choices ..................................................................................312 Creating Destination Objects..........................................................312 Copying Data..................................................................................313 Use Collation ..................................................................................314 Copy All Objects ............................................................................314 Use Default Options ......................................................................315 Choose a Script File Directory ......................................................316 Other Properties of the Copy SQL Server Objects Task ....................317 Using Methods to Include Objects in the Transfer..............................317 Creating a Copy SQL Server Objects Task in Visual Basic................320 Conclusion ..........................................................................................323 14 The File Transfer Protocol (FTP) Task 325 When to Use the File Transfer Protocol (FTP) Task ..........................326 Creating the Task and Setting Its Properties........................................327 Creating the Task in Visual Basic ........................................................329 Conclusion ..........................................................................................331 15 The Transfer Databases and Other Transfer Tasks 333 When to Use the Transfer Databases and Other Transfer Tasks ........334 Creating the Tasks and Setting Their Properties ................................335 The Source and the Destination for the Tasks................................335 The Transfer Database Task ................................................................336 The Transfer Logins Task ....................................................................337 The Transfer Jobs Task ........................................................................338 The Transfer Master Stored Procedures Task......................................339 The Transfer Error Messages Task ......................................................340 Creating the Tasks in Visual Basic ......................................................341 Conclusion ..........................................................................................342 PART IV Control Tasks 343 16 Writing Scripts for an ActiveX Script Task 345 When to Use an ActiveX Script Task ..................................................346 Creating an ActiveX Script Task ........................................................346 Dynamically Modifying DTS Properties ............................................348 Referencing a Package ..................................................................348 Referencing a Connection ..............................................................348 Referencing a Global Variable........................................................349 Referencing Steps, Tasks, and Custom Tasks ................................349 Referencing the Collections and Objects in a Transform Data Task ....................................................................................350
xiv MICROSOFT SQL SERVER 2000 DATA TRANSFORMATION SERVICES Referencing the DTS Application Object ......................................350 Objects and Properties That You Cannot Directly Reference ........351 Building a Utility to Limit Rows Processed ..................................351 Programming a Loop in a DTS Package ............................................352 Using ADO Recordsets........................................................................355 Manipulating Files and Directories ....................................................357 Writing Task Log Records ..................................................................358 Converting VB Code to VBScript........................................................359 Variable Declaration ......................................................................360 Using CreateObject for Object Variables......................................360 For Next Loops ..............................................................................360 File Access......................................................................................360 GoTo and Line Labels ....................................................................360 Error Handling................................................................................361 API Calls ........................................................................................361 Using Code as an Entry Function ..................................................361 Using VBScript Code in VB ..........................................................361 Creating an ActiveX Script Task in Visual Basic ................................361 Conclusion ..........................................................................................363 17 The Dynamic Properties Task 365 When to Use the Dynamic Properties Task ........................................366 Creating the Task and Assigning Its Properties ..................................367 Making a New Assignment ............................................................367 Choosing the Object and Property to Be Assigned........................368 Choosing the Source of Data for the Assignment..........................370 Creating a Dynamic Properties Task in Visual Basic ..........................375 Conclusion ..........................................................................................376 18 The Execute Package Task 377 When to Use the Execute Package Task..............................................378 Creating the Task and Setting Its Properties........................................379 Setting Values of Global Variables in the Child Package....................380 The NestedExecutionLevel Property of the Package ........................383 Creating and Calling a Utility DTS Package ......................................384 Creating the Task in Visual Basic ........................................................387 Conclusion ..........................................................................................390 19 The Message Queue Task 391 When to Use the Message Queue Task ..............................................392 Creating the Task and Setting Its Properties........................................393 Setting the Queue Path ..................................................................394 Sending Messages ..........................................................................394 Receiving Messages ......................................................................395 Creating the Task in Visual Basic ........................................................400 Conclusion ..........................................................................................403
xv CONTENTS 20 The Send Mail Task 405 When to Use the Send Mail Task ........................................................406 Creating the Task and Setting Its Properties........................................407 The Methods of the Send Mail Task....................................................408 Creating the Task in Visual Basic ........................................................408 Conclusion ..........................................................................................410 21 The Analysis Services Tasks 411 When to Use the Analysis Services Tasks ..........................................412 Using the Analysis Services Processing Task......................................413 Using the Data Mining Prediction Query Task ..................................417 Creating the Analysis Services Processing Task in Visual Basic ......................................................................................420 Creating the Data Mining Prediction Query Task in Visual Basic ......................................................................................421 Conclusion ..........................................................................................423 22 The Execute Process Task 425 When to Use the Execute Process Task ..............................................426 Bulk Copying from SQL Server to a Text File ..............................426 Executing a Batch File Containing osql and/or bcp Commands ..................................................................................427 Running Other Data Movement or Manipulation Applications ................................................................................427 Executing DTSRun ........................................................................427 Creating the Task and Setting Its Properties........................................428 The Execute Process Task Properties ............................................428 The GetExpandedProcessCommandLine Method of the CreateProcess2 Object................................................................428 Creating the Task in Visual Basic ........................................................429 Conclusion ..........................................................................................431 PART V DTS Packages and Steps 23 The DTS Package and Its Properties 435 Identifying DTS Packages ..................................................................436 Storing DTS Packages ........................................................................438 Saving DTS Packages to SQL Server ............................................438 Saving DTS Packages in Meta Data Services................................441 Storing DTS Packages in the File System ....................................442 Saving DTS Packages as Visual Basic Files ..................................443 Encrypting DTS Packages ..................................................................444 Retrieving Information About Packages..............................................445 Package Stored in SQL Server ......................................................445 Package Stored in Meta Data Services ..........................................449 Package Stored in Files ..................................................................450
xvi MICROSOFT SQL SERVER 2000 DATA TRANSFORMATION SERVICES Package Logs and Error Files..............................................................451 Logging to SQL Server ..................................................................454 DTS Packages as Data Sources ..........................................................460 The Data Provider DTSPackageDSO ............................................460 Setting Up a DTS Package to Be a Data Source ..........................460 Querying a DTS Package with OPENROWSET ..................................462 Registering a DTS Package as a Linked Server ............................463 Using the DTSPackageDSO Provider to Return XML from a Transform Data Task ........................................................464 Other DTS Package Object Properties and Methods ..........................465 CreationDate, CreatorComputerName, and CreatorName ..............466 PackageType ..................................................................................466 Parent ..............................................................................................466 UseOLEDBServiceComponents ........................................................467 The GetDTSVersionInfo Method ..................................................467 Conclusion ..........................................................................................467 24 Steps and Precedence Constraints 469 Steps, Tasks, and the DTS Package ....................................................470 Precedence Constraints ........................................................................471 The Three Types of Precedence ....................................................472 Creating Precedence Constraints in the DTS Package Designer ........................................................................473 Creating Precedence Constraints in Code ......................................474 The Execution Status of the Step ........................................................476 Threads and Priority of Execution ......................................................477 Package Level Thread Execution Parameters ................................477 Step Level Thread Execution Parameters ......................................478 Transactions in DTS Packages ............................................................481 Transaction Properties Set at the Package Level............................482 Transaction Settings for the Steps ..................................................484 Participation in Transactions by Connections and Tasks ..............486 A Transaction with Steps Executed in Sequence ..........................487 A Transaction with Steps Executed in Parallel ..............................490 Transactions Involving More Than One DTS Package..................493 Workflow ActiveX Scripts ..................................................................494 Script Result Constants ..................................................................494 Using the Script Results for Looping ............................................496 Step Properties for the Workflow Script ........................................496 Other Step Object Properties ..............................................................496 StartTime, FinishTime, and ExecutionTime ................................497 Description....................................................................................497 Parent ............................................................................................497 Creating a Step Using Visual Basic ....................................................497 Conclusion ..........................................................................................499
xvii CONTENTS 25 Rapid Development with the Copy Database Wizard and the DTS Import/Export Wizard 501 Two Wizards with Three Levels of Granularity ..................................502 Transferring Databases with the Copy Database Wizard ..............502 Transferring SQL Server Objects with the Import/Export Wizard ..................................................................503 Transforming Data with the Import/Export Wizard ......................503 Calling the Wizards..............................................................................504 From the Enterprise Manager ........................................................505 From the Command Line ..............................................................505 From Code ......................................................................................506 Using the Copy Database Wizard........................................................507 Choosing the Source and Destination ............................................508 Choosing the Databases and File Locations ..................................508 Choosing Other Objects to Transfer ..............................................510 The DTS Package Created by the Copy Database Wizard ............511 Creating Connections with the Import/Export Wizard........................512 Transferring SQL Server Objects ........................................................513 Setting Up Data Transformations ........................................................514 Using a Query to Specify the Data to Transfer..............................514 Copying Table(s) from the Source Database..................................516 The Destination Tables ..................................................................516 The Data Transformations ..............................................................519 Executing, Scheduling, Saving, and Replicating the Package ............520 Creating a DTS Package for Replication ......................................521 Scheduling a DTS Package for Later Execution............................522 Saving a DTS Package ..................................................................523 Completing the Import/Export Wizard ................................................523 Conclusion ..........................................................................................524 26 Managing Packages with Visual Basic and Stored Procedures 525 Working with DTS Packages in Visual Basic......................................526 Installation Requirements ..............................................................526 Saving a Package to Visual Basic ..................................................527 Setting Up the Visual Basic Design Environment..........................529 The Structure of the Generated DTS Visual Basic Code Module ......530 Header Information ........................................................................531 Declaration of Public Variables ......................................................531 Main Function ................................................................................531 Functions to Create the Tasks ........................................................535 Executing a Package from Visual Basic ..............................................536 Loading and Saving Packages ........................................................537 Handling Events ............................................................................538 Handling Errors ..............................................................................540
xviii MICROSOFT SQL SERVER 2000 DATA TRANSFORMATION SERVICES Dynamic Modification of Properties..............................................541 Implementing a Loop in the Data Transformation ........................542 Executing a Package from Visual Basic Using SQL Namespace ......542 Working with Packages Using the OLE Automation Stored Procedures..............................................................................545 Using sp_OACreate and sp_OADestroy..........................................546 Using sp_OAMethod ........................................................................547 Using sp_OAGetProperty and sp_OASetProperty..........................547 Using sp_OAGetErrorInfo ..............................................................548 Executing a Package with Stored Procedures ....................................549 Conclusion ..........................................................................................552 27 Handling Errors in a Package and Its Transformations 553 Handling Incorrect Data ......................................................................554 Analyzing the Potential Errors in the Data ....................................554 Raising Errors ................................................................................555 Correcting Errors ............................................................................557 Storing Error Records and Information..........................................557 Maintaining Transactional Consistency ........................................558 Informing Administrators of Errors................................................558 Debugging ActiveX Scripts ................................................................558 Using the Script Debugger ............................................................561 Exiting the Debugger without Terminating the Enterprise Manager......................................................................561 Conclusion ..........................................................................................563 28 High-Performance DTS Packages 565 DTS Transformation Goals..................................................................567 The Goal of Rapid Execution ........................................................567 Rapid Development ........................................................................568 Self-Documenting Data Transformations ......................................568 Maintainable Code..........................................................................568 DTS Task Performance Comparison ..................................................569 Loading from a Text File to a SQL Server Database ....................569 Loading from SQL Server 2000 to SQL Server 2000 ..................571 Performance Statistics for the Transformation Tasks ..........................573 Comparing Different Transformations in the Transform Data Task ....................................................................................573 Comparing Separate and Combined Transformations ..................574 Using Names or Ordinals in Script Transformation Columns ......575 Fetch Buffer Size, Table Lock, and Insert Batch Size ..................578 Moving Transformation Logic to the Source Query......................578 Moving Logic into a Custom Transformation................................581 Performance of the Transform Data Task and the Data Driven Query Task ......................................................................582 Choosing a Scripting Language ....................................................583
xix CONTENTS Use of Parallel Processing to Improve Performance ..........................583 Effect of Indexing on Performance......................................................584 Considering Tools Other Than DTS Because of Performance............585 Using bcp for Exporting from SQL Server to Text Files ..............585 Using Replication ..........................................................................585 Conclusion ..........................................................................................585 29 Integrating DTS with Meta Data Services 587 Why You Should Use Meta Data Services with DTS ........................588 Meta Data ......................................................................................589 The Meta Data Services Storage Choice........................................590 The DTS Browser ................................................................................590 The Browse Tab..............................................................................591 The Lineage Tab ............................................................................593 The Package Tab ............................................................................595 The Meta Data Browser ......................................................................595 Finding Information About DTS Objects and Properties ..............596 Exporting to XML ..........................................................................596 Other Meta Data Services Tools ..........................................................597 The Repository Database................................................................598 Object Models ................................................................................598 The Meta Data Services SDK ........................................................599 DWSoft’s DWGuide—A Third-Party Meta Data Tool ..................599 Configuring DTS for Column-Level Data Lineage ............................600 Package Scanning Choices ............................................................600 The RepositoryMetadataOptions Property ..................................601 Configuring DTS for Row-Level Data Lineage ..................................602 Lineage Variable Choices ..............................................................602 The LineageOptions Property ........................................................604 Saving the Lineage Variables ........................................................605 Accessing Lineage Information Programmatically ............................605 Conclusion ..........................................................................................608 PART VI Extending the Power of DTS 30 Programming with the DTS Object Model 611 Objects and Extended Objects ............................................................612 The DTS Package Object Hierarchy ..................................................613 Using Tasks and Custom Tasks ..........................................................619 Referencing a Task and a Custom Task ........................................620 Creating a New Task ......................................................................620 Object Names and Programmatic Identifiers for the Custom Tasks ..............................................................................621 Using Collections ................................................................................624 Using Properties and the Properties Collection ..................................625