SQL Server DTS

SQL Server DTS

Released August 2001
Publisher(s): Sams
ISBN: 9780735711235

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This book appeals to both developers and database administrators who are faced with the common situation of having to migrate or integrate data from one location into another.

While the first section of the book is designed for the beginner to DTS, it is assumed that the reader has at least basic database and programming experience. The second section is extensive coding, and is best used by someone with intermediate to advanced programming skills. The third section contains a mix of examples that will be useful to all levels of database administrators and programmers. For the readers who have been using DTS this section can be used as a reference section and help with DTS designs and usage. Unlike other resources available, this book covers data transformation in both SQL Server 7 and 2000.

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewers
  4. Acknowledgments
  5. Tell Us What You Think
  6. Preface
  7. Introduction
    1. Scope and Audience
    2. Organization and Layout
      1. Part I, “Data Transformation Services—The Tool”
      2. Part II, “Automating Data Transformation Services”
      3. Part III, “DTS and the Business Intelligence Process”
      4. Part IV, “Using the Tool—Case Studies”
    3. Conventions
    4. How to Use This Book
  8. I. Data Transformation Services—The Tool
    1. 1. DTS in the ETL World
      1. DTS and Traditional Technologies
        1. BCP
        2. Replication
        3. Transact-SQL (T-SQL)
      2. DTS and Today’s Technologies
        1. DTS and Commerce Server
        2. DTS and BizTalk Server
      3. Real-World Scenarios Used in This Book—About Sparks Electronics
      4. Summary
    2. 2. DTS—An Overview
      1. The DTS Package
      2. Building a Package
        1. The Scenario
        2. Package Design
          1. Step 1: Create the Basic Package
          2. Step 2: Create the Data Connections
          3. Step 3: Create Global Variables
          4. Step 4: Prepare Spreadsheet and Extract Reporting Date
          5. Step 5: Load Actuals Data
          6. Step 6: Load Variance Report Data
          7. Step 7: Create Error Message
          8. Step 8: Add Workflow
      3. Summary
    3. 3. Connections
      1. Database Management System Connections
        1. Microsoft OLE DB Provider for SQL Server
        2. Microsoft ODBC Driver for Oracle
      2. File System Database Connections
      3. Application Connections
      4. Text File Connections
        1. Text File (Source and Destination)
        2. HTML File (Source)
      5. Microsoft Data Links
        1. UDL Link Name
        2. Data Link Properties
      6. Other Connections
      7. Editing Connections
      8. Summary
    4. 4. DTS Tasks
      1. Using DTS Tasks
      2. Using the Transform Data Task
        1. Setting up the Source Data
          1. Using the Query Designer
          2. Using Parameters in the Query
        2. Setting up the Destination Table
        3. Transformations
          1. Understanding the Built-In Transformation Types
          2. Understanding the Transformation Flags
          3. The ActiveX Script Property Window
          4. Transform Status Codes
          5. Mapping Transformation Columns
        4. Lookups
        5. Transform Data Task Properties Options
      3. Data Driven Query Task
        1. Understanding the Role of the Binding Table
        2. The Driven Queries
        3. Data Driven Query Task Options
      4. The Multiphase Data Pump
        1. Gaining Access to the Multiphase Data Pump
        2. Understanding the Data Pump Phases
      5. Bulk Insert Task
        1. Bulk Insert Task Options
      6. Summary
    5. 5. More DTS Tasks
      1. Execute SQL Task
        1. Using the SQL Result Sets
          1. Saving a Row
          2. Saving an Entire Result Set
      2. ActiveX Script Task
      3. Execute Process Task
      4. Execute Package Task
        1. Inner Package Global Variables
        2. Outer Package Global Variables
      5. Dynamic Properties Task
      6. Send Mail Task
        1. Sending Mail
        2. Configuring Mail Services for SQL Server
      7. File Transfer Protocol Task
      8. Message Queue Task
        1. Sending Messages
        2. Receiving Messages
      9. Copy SQL Server Objects Task
      10. Transfer Databases Task
      11. Transfer Logins Task
      12. Transfer Master Stored Procedures Task
      13. Transfer Jobs Task
      14. Transfer Error Messages Task
      15. Analysis Services Processing Task
        1. Processing Options
        2. Processing Cubes
        3. Processing Mining Models
      16. Data Mining Task
      17. Summary
    6. 6. DTS Workflows
      1. How to Use DTS Workflows
      2. Precedence Properties
        1. On Completion
        2. On Success
        3. On Failure
      3. Workflow Properties
        1. Transaction Properties
        2. Execution Properties
          1. Execute on Main Thread
          2. Close Connection on Completion
          3. Fail Package on Step Failure
          4. DSO Rowset Provider
          5. Querying Package Data
            1. Using OPENROWSET to Query Package Data
            2. Using Distributed Queries to Query Package Data
          6. Disable This Step
          7. Task Priority
        3. ActiveX Script
          1. Setting up the Sample Package
          2. Stopping Execution
          3. Restarting Execution
          4. Looping Execution
      4. Summary
    7. 7. Package
      1. General Properties
      2. Global Variables
      3. Logging
      4. Error Handling
      5. Transactions
        1. Transaction Scenarios
          1. Sequential Tasks
          2. Parallel Tasks
          3. Branching Workflows
          4. Checkpointing for Multiple Transactions
          5. Inheriting Transactions
      6. Miscellaneous Properties
        1. Lineage
        2. Scanning Options
        3. OLE DB
      7. Disconnected Edit
      8. Package Storage Locations
        1. SQL Server
        2. Meta Data Services
        3. Structured Storage File
        4. Visual Basic File
      9. Package Security
      10. Package Execution
      11. Package Scheduling
      12. Package Deletion
      13. Summary
    8. 8. Putting It All Together—An Extended DTS Example
      1. Setting the Scenario
      2. Solution
        1. Development Environment
        2. Global, Local Configuration, and Transactional Data
      3. Building the Reporting Database and Creating Data Extract Packages
        1. Building the Reporting Database
        2. Building a Package to Import Configuration Data to tempdb
        3. Building a Package to Import Site Data to tempdb
      4. Moving Data from tempdb into the Reporting Database
        1. Manipulating Global Configuration Data
        2. Manipulating Local Configuration Data
        3. Manipulating Large Data Sets
      5. Exporting Data and Generating Reports
        1. Creating an Excel spreadsheet
        2. Simple HTML Report
        3. Using HTML to Report Job History
      6. DTS2000Mfg_Production—Pulling It All Together
        1. Scheduling the DTS Package
        2. Tracking Job History
        3. Controlling Scheduled DTS Jobs
      7. Enhancing Performance
        1. General Server Performance
        2. Avoid Using DTS
        3. Using Bulk Insert and BCP
        4. Avoid ExecuteOnMainThread
        5. Avoid ActiveX Scripts
        6. Enhancing Data-Pump Performance
        7. Data-Driven Queries
      8. Summary
  9. II. Automating Data Transformation Services
    1. 9. Building a Package Using Visual Basic
      1. The Scenario
      2. Generating a VB DTS Package with SQL 2000 DTS Designer
      3. Including the DTS Script in a VB Application
        1. DTS Dynamic Link Libraries
        2. VB Threading Considerations
        3. Connection Considerations
        4. Compiling and Running the Package
      4. The DTS Object Model
      5. The DTS VB Script
        1. Setting Package Options
        2. Defining the Package’s GlobalVariables Collection
        3. Defining Package Connections
        4. Defining Package Steps
        5. Defining Package Constraints
        6. Defining Package Tasks
        7. Executing the Package
        8. Adding an ExecuteSQLTask
        9. Adding an ActiveScriptTask
        10. Adding a DataPumpTask
          1. Defining the DataPumpTask Options
          2. Defining the DataPumpTask’s Transformation Script
          3. Defining the DataPumpTask’s Transformation Script Columns
          4. Defining the DataPumpTask’s Transformation Script ActiveX Script
          5. Defining the DataPumpTask’s Transformation Script Lookup
          6. Saving the DataPumpTask to the Package’s Tasks Collection
            1. ExecuteSQLTask
      6. Building Your Solution
        1. Modifying the VB Application
        2. Running the Application
        3. Viewing the Output
      7. Summary
    2. 10. Interacting with a Package Using Visual Basic
      1. Executing DTS Packages in Visual Basic
      2. Making Best-Practice Upgrades to the VB .bas Files
      3. Loading and Saving Packages in Visual Basic
        1. Package Load and Save Repository Methods
        2. Package Load and Save SQL Server Methods
        3. Package Load and Save Storage File Methods
      4. Handling DTS Package Events in Visual Basic
        1. OnStart Event
        2. OnFinish Event
        3. OnProgress Event
        4. OnQueryCancel Event
        5. OnError Event
      5. Handling DTS Package Errors in Visual Basic
      6. Processing Multiple Input Files
        1. Scenario
        2. Solution
      7. Processing Multiple Spreadsheets
        1. Scenario
        2. Solution
      8. Loading XML via DTS
        1. Scenario
        2. Solution
      9. Processing Outlook via DTS
        1. Scenario
        2. Solution
      10. Summary
  10. III. DTS and the Business Intelligence Process
    1. 11. Introduction to Data Warehousing and Analysis Services
      1. Data Warehouse Versus Operational Transaction-Oriented Database
        1. Dimensional Modeling
        2. Business Modeling
        3. Data Marts
        4. The Operational Data Store
        5. Staging Area
      2. Dimensional Modeling of Business Process
        1. Dimension Table Overview
          1. Dimension Granularity
          2. Slowly Changing Dimensions
        2. Fact Table Overview
          1. Measures
          2. Dimension Foreign Keys
        3. Assembling the Pieces
      3. Populating the Data Mart
        1. Creating the Data Staging Area
        2. Cleansing and Transforming Data
      4. Introduction to Analysis Services
        1. Analysis Services Architecture
        2. Analysis Services Database
          1. Cubes
          2. Dimensions
        3. Dimension Levels
        4. Parent-Child Dimensions
          1. Dimension Members
          2. Member Properties
        5. Measures and Aggregations
          1. Calculated Members
      5. Summary
    2. 12. Building a Data Warehouse with DTS and Analysis Services
      1. The Data Warehouse Process
        1. Step 1: Create the Connections to the Data Sources
        2. Step 2: Define a Connection to the Data Mart
        3. Step 3: Define the Queries to Extract Data from the Sources Previously Defined
        4. Step 4: Define the Transformation and Load Steps
      2. Building a Cube with Analysis Services
        1. Setting up a Data Source
        2. Creating a Cube
      3. Summary
    3. 13. DTS and Data Mining
      1. Analyzing Data
        1. Data Mining Model
          1. Decision Trees
          2. Clustering
        2. Data Mining Model Content
        3. Data Mining Exercise
        4. Let the Analysis Begin
      2. Summary
  11. IV. Using the Tool— Case Studies
    1. 14. Custom Error Logging with DTS
      1. Solution Design
      2. Implementation
        1. Create Logging Table in SQL Server
        2. Create Connection to Logging Table
        3. Change the Shipping Budget Excel Connection to Shipping Budget Error
        4. Create the Lookup Query to Insert the Log Transactions
        5. Add Error-Handling Code to the Transform Data Script
          1. Notifying the Administrator of Errors
        6. Execute Package
      3. Error-Logging Design Strengths and Weaknesses
        1. Custom Logging Using Execute SQL and Execute Scripting Tasks
          1. Execute SQL Task
          2. Execute Script Task
      4. Summary
    2. 15. Managing Distributed Databases with DTS and Message Queues
      1. Example Design
      2. Implementation
        1. Step 1: Create Queue
        2. Step 2: Create the Recipient Database
        3. Step 3: Create the Sending Data Package
        4. Step 4: Create an ActiveX Script Task
        5. Step 5: Send the Message with the XML String to the Message Queue
        6. Step 6: Make the Package Loop to Create Many Records
      3. Create Package to Receive and Process the Messages
        1. Step 1: Create the Stored Procedure
        2. Step 2: Create a New Package
        3. Step 3: Add Connections
        4. Step 4: Add a Transform Data Connection
        5. Step 5: Create a Looping Mechanism
        6. Step 6: Add Workflow.
      4. Summary
    3. 16. Creating Your Own Custom Task
      1. Setting up a Custom Task-Development Project
      2. The Custom Task Interface
        1. The Description Property
        2. The Name Property
        3. The Properties Collection
        4. The Execute Method
          1. The pPackage Parameter
          2. The pPackageEvents Parameter
          3. The pPackageLog Parameter
          4. The pTaskResult Parameter
        5. Generate HTML Task: Execute Method
      3. Creating the Property Page
      4. The Custom Task UI Interface
        1. The Initialize Method
        2. The New Method
        3. The Edit Method
        4. The Delete Method
        5. The Help Method
        6. The GetUIInfo Method
        7. The CreateCustomToolTip Method
      5. Adding a Custom Icon to the Custom Task
      6. Registering the Custom Task
      7. Using the Custom Task
      8. Summary
    4. 17. Executing a DTS Package from a Stored Procedure
      1. Executing DTS Packages from T-SQL
      2. Executing DTS Packages with xp_cmdshell
        1. Using dtsrunui
        2. Executing DTS Packages with OLE Automation
          1. sp_OACreate
            1. sp_OAMethod
            2. sp_OADestroy
            3. sp_displayoaerrorinfo
          2. Putting It Together
        3. OLE Automation Versus xp_cmdshell
      3. Summary
    5. 18. Data Lineage
      1. Introduction to Microsoft’s Meta Data Services
      2. Meta Data Services and DTS
        1. Package Property Settings
          1. Scanning Options
        2. Saving DTS Packages as Meta Data
        3. Viewing DTS Packages as Meta Data
        4. Using the Meta Data Browser
      3. How to Use Data Lineage
        1. Row-Level Data Lineage
        2. Column-Level Data Lineage
        3. Data Lineage Applied to a Package
          1. Overview of the Package
          2. Steps to Set Up Meta Data
          3. Viewing the Data Lineage
      4. Summary

Product information

  • Title: SQL Server DTS
  • Author(s):
  • Release date: August 2001
  • Publisher(s): Sams
  • ISBN: 9780735711235