Home
Tour
Download
Purchase
Local Cube Information Center
Samples
Support
About Us
Contact

Microsoft® SQL Server™ 2000 Data Transformation Services

Table of Contents

Introduction

  • Using DTS in SQL Server 7.0 and SQL Server 2000
  • The Code Samples
  • Keeping Current with Information on DTS

Part I - Getting Started with DTS

Chapter 1 - A Quick Look at DTS

  • A High-Performance Data Pump—and a Whole Lot More
  • Using the DTS Designer to Create Complex Data Transformations
    • The Structure of a DTS Package
    • Creating Connections
    • Creating Tasks
    • Setting the Workflow
  • Saving and Retrieving DTS Packages
    • SQL Server Storage
    • SQL Server Meta Data Services Storage
    • Structured Storage File
    • Visual Basic File
    • Package Versions
  • How to Execute a DTS Package
    • From the DTS Designer
    • From the Wizards
    • From the Enterprise Manager
    • From Visual Basic Code
    • With the OLE Automation System Stored Procedures
    • As a Data Provider
    • Using the DTSRun Command Prompt Utility
    • Using the DTSRun Utility for Windows
    • From Another DTS Package
    • Scheduling a DTS Package
    • The Execution Context for a DTS Package
  • Using the DTS Object Model for Programmatic Control
    • The DTS Object Model
    • Using the DTS Object Model with the Dynamic Properties Task
    • Using the DTS Object Model with Disconnected Edit
    • Programming DTS with VBScript
    • Programming DTS with Visual Basic
    • Programming DTS with VC++
  • DTS Templates
    • Using Templates
    • Creating Templates
  • Using Wizards for Rapid Application Development
    • Copy Database Wizard
    • DTS Import/Export Wizard
  • Practical Uses for DTS
    • Upgrading a Database from Microsoft Access to SQL Server
    • Consolidating Data from Multiple Sources
    • Initial and Periodic Loading of a Data Mart or a Data Warehouse
    • Reporting on Data from Transaction Processing Systems
    • Building an Interface to the Web for a Legacy System
    • Archiving a Database
    • Analyzing Internet Clickstream Data
    • Importing and Exporting with XML
  • Conclusion

Chapter 2 - DTS Enhancements for SQL Server 2000

  • Top Ten New DTS Features
  • The New DTS Tasks
    • Dynamic Properties Task
    • The File Transfer Protocol (FTP) Task
    • The Execute Package Task
    • The Message Queue Task
    • The Parallel Data Pump Task
    • The Analysis Services Processing Task
    • The Data Mining Prediction Query Task
    • The Transfer Databases Task
    • The Transfer Master Stored Procedures Task
    • The Transfer Jobs Task
    • The Transfer Logins Task
    • The Transfer Error Messages Task
  • Transformation Task Enhancements
    • Multiple Phases in the Data Transformation Tasks
    • Package Object Browser When Writing Scripts
  • The New Data Transformations
    • Using Parameters in the Source Query
    • New Error File Options
    • Lookups Can Now Modify Data
  • New Features in Other Tasks
    • Using Input Parameters in the Execute SQL Task
    • Assigning the Result of the Execute SQL Task to Parameters
    • Assigning the Result of the Execute SQL Task to Recordset
    • Package Object Browser for Writing ActiveX Scripts
    • Auto-Generating a Format File in the Bulk Insert Task
  • Other New Features in DTS
    • Copy Database Wizard
    • Save To Visual Basic File
    • Setting Values of the Package Global Variables with DTSRun
    • DTSRunUI—A Windows Interface for DTSRun
    • Package Templates
    • Disconnected Edit
    • Option Explicit for Global Variables
    • Just-In-Time Debugging
    • Turn On Package Cache
    • Executing Individual Steps in the Package Designer
    • DTS Package Logs
  • Other New Features in SQL Server 2000 That Enhance Data Transformation
    • Integration with XML
    • Indexed Views
    • User-Defined Functions
  • Conclusion

Chapter 3 - DTS and the Flow of Data Through the Enterprise

  • Enterprise Data Structure and Data Flow
  • Business Drivers for Enterprise Data Transformation
  • Ways to Use Data
    • Transaction Processing
    • Business Analysis
    • Maintaining History
  • Sources of Data
    • On Line Transaction Processing (OLTP) Systems
    • Web Clickstream Data
    • Data from Outside the Organization
    • The Data Warehouse
    • Results from Business Analysis Systems
  • Meta Data
  • Types of Data Storage
    • Text Files
    • XML
    • Spreadsheets
    • Relational Database Management Systems
    • Multidimensional Database Management Systems (OLAP)
  • Conclusion

Chapter 4 - Using DTS to Move Data into a Data Mart

  • Multidimensional Data Modeling
    • Differences Between Relational Modeling and Multidimensional Modeling
  • The Fact Table
    • Choosing the Measures
    • Choosing the Level of Summarization for the Measures
  • The Dimension Tables
    • The Primary Key in a Dimension Table
    • Levels of the Dimension Hierarchy
    • Attributes of the Dimension
    • The Time Dimension
    • Subordinate Dimension Keys
  • Loading the Star Schema
    • Loading Data into a Staging Area
    • Loading the Dimension Tables
    • Updating the Subordinate Dimension Keys
    • Loading the Fact Table
    • Using the Data
  • Avoiding Updates to Dimension Tables
  • Conclusion

Part II - DTS Connections and the Data Transformation Tasks

Chapter 5 - DTS Connections

  • Creating DTS Connections
  • DTS Connection Properties
    • Properties That Identify a DTS Connection
    • Properties That Determine Access to the Data Source
    • Other Properties
  • Creating Connections for Different OLE DB Providers
    • The SQL Server OLE DB Provider
    • Text File Connections
    • The OLE DB Provider for OLAP Services
    • Other OLE DB Providers
  • Modifying Data Connections Dynamically
  • Performance Issues with DTS Connections
  • Conclusion

Chapter 6 - The Transform Data Task

  • When to Use the Transform Data Task
  • Creating a New Transform Data Task
    • Using the Package Designer
    • Using the DTS Import/Export Wizard
    • Using Code
  • The Description and Name of the Task
  • The Source of a Transform Data Task
    • Text File Source
    • SQL Table, View, or Query for a Relational Database Source
    • MDX Query for a Multidimensional Cube Source
    • Using XML as the Source
    • Using Parameters in a Source Query
    • DataPumpTask Source Properties
  • The Destination of a Transform Data Task
    • Creating a New Destination Table
    • Text File Destination
    • DataPumpTask Destination Properties
  • Mapping Source Columns to Destination Columns
    • Transformation Flags
    • Testing a Transformation
    • The Collections That Implement a Transformation
    • Other Properties of a Transformation
  • The Transformation Types
    • The DateTime String
    • Uppercase Strings, Lowercase Strings, and Copy Column
    • Middle of String and Trim String
    • Read File
    • Write File
    • ActiveX Script
    • Custom Transformation
  • Other Properties of the Transform Data Task
    • Error Handling Properties
    • Data Movement Properties
    • Options for Improving Performance with SQL Server Destinations
  • Column Properties
  • Creating a Transform Data Task in Visual Basic
  • Creating Transformations in Code
    • Choosing a Transformation Type in Code
    • Creating the Transformation and Its Columns
    • Copy Column, Uppercase, and Lowercase Transformations
    • Trim String and Middle of String Transformations
    • Read File and Write File Transformations
    • DateTime Transformations
    • ActiveX Transformations
    • A Sample Application with All the Transformations
  • Using the Transform Data Task as a FreeStanding Icon
  • Conclusion

Chapter 7 - Writing ActiveX Scripts for a Transform Data Task

  • When You Should Use the ActiveX Script Transformation
    • Deciding Between One Task and Many
    • Using the Variety of Transformation Types
  • Transformation ActiveX Scripts Basics
  • The Transformation ActiveX Script Development Environment
  • Choosing a Scripting Language
  • Setting the DTS Transformation Status
    • DTSTransformStat_OK
    • DTSTransformStat_SkipRow
    • DTSTransformStat_SkipFetch
    • DTSTransformStat_SkipInsert
    • DTSTransformStat_DestDataNotSet
    • DTSTransformStat_Info
    • DTSTransformStat_OKInfo
    • DTSTransformStat_SkipRowInfo
    • DTSTransformStat_Error
    • DTSTransformStat_ErrorSkipRow
    • DTSTransformStat_ExceptionRow
    • DTSTransformStat_AbortPump
    • DTSTransformStat_NoMoreRows
  • Creating and Using Local Variables
    • Variable Types
    • Object Variables
    • Using Option Explicit
  • Creating and Using Global Variables
    • Creating Global Variables in the User Interface
    • Creating Global Variables in an ActiveX Script
    • Case Sensitivity of Global Variables and Option Explicit
    • The Lock and Unlock Methods of the GlobalVariable2 Object
  • Creating and Using Lookups
    • Creating Lookups with the User Interface
    • Creating Lookup Objects in an ActiveX Script
    • Using a Lookup in an ActiveX Script
    • Using a Lookup to Modify Data
  • Using ActiveX Scripts or Modifying the Source Query
    • Simple Assignment of Fields
    • String Manipulation
    • Handling Unknown Values
    • Looking Up an Unknown Value
    • Using an Outer Join to Protect Against Missing Data
    • Merging Data from Two Sources with a Full Outer Join
  • Separating Information from One Record into Several Records
  • Combining Information from Several Records into One
  • Conclusion

Chapter 8 - The Data Driven Query Task

  • When to Use the Data Driven Query Task
  • Creating a Data Driven Query Task
  • Transformation Status Constants for the Data Driven Query Task
  • A Data Driven Query Example
  • Creating a Data Driven Query Task in Visual Basic
  • Conclusion

Chapter 9 - The Multiphase Data Pump

  • Enabling the Multiphase Data Pump
  • Programmatic Flow with Multiple Phases
  • Using the Phases
    • Pre Source Phase
    • Row Transform Phase
    • Post Row Transform Phase
    • On Transform Failure Subphase
    • On Insert Failure Subphase
    • On Insert Success Subphase
    • On Batch Complete Phase
    • Post Source Data Phase
    • Pump Complete Phase
  • Creating a COM Object with Visual C++ to Program the Phases
  • Creating a Multiphase Data Pump in Code
    • The TransformPhases Property
    • Setting the Entrance Functions
  • Conclusion

Chapter 10 - The Parallel Data Pump Task

  • Hierarchical Rowsets
  • When to Use the Parallel Data Pump Task
  • The Collections and the Properties of the Parallel Data Pump Task
  • The Transformation Modes
    • Hierarchical Mode
    • Flattened Mode
    • Data Driven Query Mode
    • The TransformationSetOptions Property
  • Creating a Parallel Data Pump Task in Visual Basic
  • A User Interface for the Parallel Data Pump Task
  • Conclusion

Part III - Other Data Movement and Manipulation Tasks

Chapter 11 - The Bulk Insert Task

  • When to Choose the Bulk Insert Task Over the Transform Data Task
  • Creating a Bulk Insert Task
  • The Destination for a Bulk Insert Task
  • The Source for a Bulk Insert
    • Fixed-Length Text Files
    • Delimited Text Files
  • Format Files
    • Format File Structure
    • Generating a Format File
  • Reconciling Differences Between the Source and the Destination
    • Extra Fields in the Data Destination Table
    • Rearranging Fields When Moving from Source to Destination
    • Extra Fields in the Source Text File
  • Other Properties of the Bulk Insert Task
    • Check Constraints
    • Keep Nulls
    • Enable Identity Insert
    • Table Lock
    • Sorted Data
    • Code Page
    • Data File Type
    • Insert Commit Size
    • Maximum Errors
    • Only Copy Selected Rows, Starting with Row, and Stopping at Row
  • Creating a Bulk Insert Task in Visual Basic
  • Conclusion

Chapter 12 - The Execute SQL Task

  • When to Use the Execute SQL Task
  • Creating the Execute SQL Task
  • Writing Queries for Different Database Systems
  • Using Input Parameters in Execute SQL Tasks
  • Using Output Parameters for Row Values
  • Using an Output Parameter for the Rowset
  • Dynamically Modifying the SQL Statement
  • Using the Execute SQL Task to Execute a DTS Package from a Remote Server
  • Creating an Execute SQL Task in Visual Basic
  • Conclusion

Chapter 13 - The Copy SQL Server Objects Task

  • When to Use the Copy SQL Server Objects Task
  • The Source and the Destination
  • Transfer Choices
    • Creating Destination Objects
    • Copying Data
    • Use Collation
    • Copy All Objects
    • Use Default Options
    • Choose a Script File Directory
  • Other Properties of the Copy SQL Server Objects Task
  • Using Methods to Include Objects in the Transfer
  • Creating a Copy SQL Server Objects Task in Visual Basic
  • Conclusion

Chapter 14 - The File Transfer Protocol (FTP) Task

  • When to Use the File Transfer Protocol (FTP) Task
  • Creating the Task and Setting Its Properties
  • Creating the Task in Visual Basic
  • Conclusion

Chapter 15 - The Transfer Databases and Other Transfer Tasks

  • When to Use the Transfer Databases and Other Transfer Tasks
  • Creating the Tasks and Setting Their Properties
    • The Source and the Destination for the Tasks
  • The Transfer Database Task
  • The Transfer Logins Task
  • The Transfer Jobs Task
  • The Transfer Master Stored Procedures Task
  • The Transfer Error Messages Task
  • Creating the Tasks in Visual Basic
  • Conclusion

Part IV - Control Tasks

Chapter 16 - Writing Scripts for an ActiveX Script Task

  • When to Use an ActiveX Script Task
  • Creating an ActiveX Script Task
  • Dynamically Modifying DTS Properties
    • Referencing a Package
    • Referencing a Connection
    • Referencing a Global Variable
    • Referencing Steps, Tasks, and Custom Tasks
    • Referencing the Collections and Objects in a Transform Data Task
    • Referencing the DTS Application Object
    • Objects and Properties That You Cannot Directly Reference
    • Building a Utility to Limit Rows Processed
  • Programming a Loop in a DTS Package
  • Using ADO Recordsets
  • Manipulating Files and Directories
  • Writing Task Log Records
  • Converting VB Code to VBScript
    • Variable Declaration
    • Using CreateObject for Object Variables
    • For Next Loops
    • File Access
    • GoTo and Line Labels
    • Error Handling
    • API Calls
    • Using Code as an Entry Function
    • Using VBScript Code in VB
  • Creating an ActiveX Script Task in Visual Basic
  • Conclusion

Chapter 17 - The Dynamic Properties Task

  • When to Use the Dynamic Properties Task
  • Creating the Task and Assigning Its Properties
    • Making a New Assignment
    • Choosing the Object and Property to Be Assigned
    • Choosing the Source of Data for the Assignment
  • Creating a Dynamic Properties Task in Visual Basic
  • Conclusion

Chapter 18 - The Execute Package Task

  • When to Use the Execute Package Task
  • Creating the Task and Setting Its Properties
  • Setting Values of Global Variables in the Child Package
  • The NestedExecutionLevel Property of the Package
  • Creating and Calling a Utility DTS Package
  • Creating the Task in Visual Basic
  • Conclusion

Chapter 19 - The Message Queue Task

  • When to Use the Message Queue Task
  • Creating the Task and Setting Its Properties
    • Setting the Queue Path
    • Sending Messages
    • Receiving Messages
  • Creating the Task in Visual Basic
  • Conclusion

Chapter 20 - The Send Mail Task

  • When to Use the Send Mail Task
  • Creating the Task and Setting Its Properties
  • The Methods of the Send Mail Task
  • Creating the Task in Visual Basic
  • Conclusion

Chapter 21 - The Analysis Services Tasks

  • When to Use the Analysis Services Tasks
  • Using the Analysis Services Processing Task
  • Using the Data Mining Prediction Query Task
  • Creating the Analysis Services Processing Task in Visual Basic
  • Creating the Data Mining Prediction Query Task in Visual Basic
  • Conclusion

Chapter 22 - The Execute Process Task

  • When to Use the Execute Process Task
    • Bulk Copying from SQL Server to a Text File
    • Executing a Batch File Containing osql and/or bcp Commands
    • Running Other Data Movement or Manipulation Applications
    • Executing DTSRun
  • Creating the Task and Setting Its Properties
    • The Execute Process Task Properties
    • The GetExpandedProcessCommandLine Method of the CreateProcess2 Object
  • Creating the Task in Visual Basic
  • Conclusion

Part V - DTS Packages and Steps

Chapter 23 - The DTS Package and Its Properties

  • Identifying DTS Packages
  • Storing DTS Packages
    • Saving DTS Packages to SQL Server
    • Saving DTS Packages in Meta Data Services
    • Storing DTS Packages in the File System
    • Saving DTS Packages as Visual Basic Files
  • Encrypting DTS Packages
  • Retrieving Information About Packages
    • Package Stored in SQL Server
    • Package Stored in Meta Data Services
    • Package Stored in Files
  • Package Logs and Error Files
    • Logging to SQL Server
  • DTS Packages as Data Sources
    • The Data Provider DTSPackageDSO
    • Setting Up a DTS Package to Be a Data Source
    • Querying a DTS Package with OPENROWSET
    • Registering a DTS Package as a Linked Server
    • Using the DTSPackageDSO Provider to Return XML from a Transform Data Task
  • Other DTS Package Object Properties and Methods
    • CreationDate, CreatorComputerName, and CreatorName
    • PackageType
    • Parent
    • UseOLEDBServiceComponents
    • The GetDTSVersionInfo Method
  • Conclusion

Chapter 24 - Steps and Precedence Constraints

  • Steps, Tasks, and the DTS Package
  • Precedence Constraints
    • The Three Types of Precedence
    • Creating Precedence Constraints in the DTS Package Designer
    • Creating Precedence Constraints in Code
  • The Execution Status of the Step
  • Threads and Priority of Execution
    • Package Level Thread Execution Parameters
    • Step Level Thread Execution Parameters
  • Transactions in DTS Packages
    • Transaction Properties Set at the Package Level
    • Transaction Settings for the Steps
    • Participation in Transactions by Connections and Tasks
    • A Transaction with Steps Executed in Sequence
    • A Transaction with Steps Executed in Parallel
    • Transactions Involving More Than One DTS Package
  • Workflow ActiveX Scripts
    • Script Result Constants
    • Using the Script Results for Looping
    • Step Properties for the Workflow Script
  • Other Step Object Properties
    • StartTime, FinishTime, and ExecutionTime
    • Description
    • Parent
  • Creating a Step Using Visual Basic
  • Conclusion

Chapter 25 - Rapid Development with the Copy Database Wizard and the DTS Import/Export Wizard

  • Two Wizards with Three Levels of Granularity
    • Transferring Databases with the Copy Database Wizard
    • Transferring SQL Server Objects with the Import/Export Wizard
    • Transforming Data with the Import/Export Wizard
  • Calling the Wizards
    • From the Enterprise Manager
    • From the Command Line
    • From Code
  • Using the Copy Database Wizard
    • Choosing the Source and Destination
    • Choosing the Databases and File Locations
    • Choosing Other Objects to Transfer
    • The DTS Package Created by the Copy Database Wizard
  • Creating Connections with the Import/Export Wizard
  • Transferring SQL Server Objects
  • Setting Up Data Transformations
    • Using a Query to Specify the Data to Transfer
    • Copying Table(s) from the Source Database
    • The Destination Tables
    • The Data Transformations
  • Executing, Scheduling, Saving, and Replicating the Package
    • Creating a DTS Package for Replication
    • Scheduling a DTS Package for Later Execution
    • Saving a DTS Package
  • Completing the Import/Export Wizard
  • Conclusion

Chapter 26 - Managing Packages with Visual Basic and Stored Procedures

  • Working with DTS Packages in Visual Basic
    • Installation Requirements
    • Saving a Package to Visual Basic
    • Setting Up the Visual Basic Design Environment
  • The Structure of the Generated DTS Visual Basic Code Module
    • Header Information
    • Declaration of Public Variables
    • Main Function
    • Functions to Create the Tasks
  • Executing a Package from Visual Basic
    • Loading and Saving Packages
    • Handling Events
    • Handling Errors
    • Dynamic Modification of Properties
    • Implementing a Loop in the Data Transformation
  • Executing a Package from Visual Basic Using SQL Namespace
  • Working with Packages Using the OLE Automation Stored Procedures
    • Using sp_OACreate and sp_OADestroy
    • Using sp_OAMethod
    • Using sp_OAGetProperty and sp_OASetProperty
    • Using sp_OAGetErrorInfo
  • Executing a Package with Stored Procedures
  • Conclusion

Chapter 27 - Handling Errors in a Package and Its Transformations

  • Handling Incorrect Data
    • Analyzing the Potential Errors in the Data
    • Raising Errors
    • Correcting Errors
    • Storing Error Records and Information
    • Maintaining Transactional Consistency
    • Informing Administrators of Errors
  • Debugging ActiveX Scripts
    • Using the Script Debugger
    • Exiting the Debugger without Terminating the Enterprise Manager
  • Conclusion

Chapter 28 - High-Performance DTS Packages

  • DTS Transformation Goals
    • The Goal of Rapid Execution
    • Rapid Development
    • Self-Documenting Data Transformations
    • Maintainable Code
  • DTS Task Performance Comparison
    • Loading from a Text File to a SQL Server Database
    • Loading from SQL Server 2000 to SQL Server 2000
  • Performance Statistics for the Transformation Tasks
    • Comparing Different Transformations in the Transform Data Task
    • Comparing Separate and Combined Transformations
    • Using Names or Ordinals in Script Transformation Columns
    • Fetch Buffer Size, Table Lock, and Insert Batch Size
    • Moving Transformation Logic to the Source Query
    • Moving Logic into a Custom Transformation
    • Performance of the Transform Data Task and the Data Driven Query Task
    • Choosing a Scripting Language
  • Use of Parallel Processing to Improve Performance
  • Effect of Indexing on Performance
  • Considering Tools Other Than DTS Because of Performance
    • Using bcp for Exporting from SQL Server to Text Files
    • Using Replication
  • Conclusion

Chapter 29 - Integrating DTS with Meta Data Services

  • Why You Should Use Meta Data Services with DTS
    • Meta Data
    • The Meta Data Services Storage Choice
  • The DTS Browser
    • The Browse Tab
    • The Lineage Tab
    • The Package Tab
  • The Meta Data Browser
    • Finding Information About DTS Objects and Properties
    • Exporting to XML
  • Other Meta Data Services Tools
    • The Repository Database
    • Object Models
    • The Meta Data Services SDK
    • DWSoft's DWGuide—A Third-Party Meta Data Tool
  • Configuring DTS for Column-Level Data Lineage
    • Package Scanning Choices
    • The RepositoryMetadataOptions Property
  • Configuring DTS for Row-Level Data Lineage
    • Lineage Variable Choices
    • The LineageOptions Property
    • Saving the Lineage Variables
  • Accessing Lineage Information Programmatically
  • Conclusion

Part VI - Extending the Power

Chapter 30 - Programming with the DTS Object Model

  • Objects and Extended Objects
  • The DTS Package Object Hierarchy
  • Using Tasks and Custom Tasks
    • Referencing a Task and a Custom Task
    • Creating a New Task
    • Object Names and Programmatic Identifiers for the Custom Tasks
  • Using Collections
  • Using Properties and the Properties Collection
  • The DTS Application Object Hierarchy
  • Documenting the Connections in a DTS Package
  • Conclusion

Chapter 31 - Creating a Custom Task in

  • When You Should Create a New Custom Task
  • Getting Started
  • Implementing the Custom Task Interface
    • The Name Property
    • The Description Property
    • The Properties Collection
    • The Execute Method
  • Implementing the Custom Task User Interface
    • The New Method
    • The Edit Method
    • Initialize
    • Help
  • Events, Errors, and Logs
  • Registering a Custom Task
  • Using a Custom Task in a DTS Package
  • The Find File Task
  • The LocalCubeTask
  • Conclusion

Chapter 32 - Creating a Custom Transformation with VC++

  • Why You Should Create a Custom Transformation
  • The Data Pump Interfaces
  • Preparing the C++ Custom Transformation Development Environment
  • Creating a Custom Transformation
    • Starting the Project
    • Adding a Property to the Custom Transformation
    • Adding Code for the PreValidateSchema Method
    • Adding Code for the ValidateSchema Method
    • Adding Code for the ProcessPhase Method
    • Adding the Project's Error Definitions
    • Registering the Custom Transformation
  • Conclusion

Index

© Copyright Macmillan USA. All rights reserved.



sdg