Book

Description

Copyright 2000

Dimensions: 7.375 X 9.125

Pages: 744

Edition: 1st

Book

ISBN-10: 0-672-32011-8

ISBN-13: 978-0-672-32011-8

Microsoft SQL Server 2000 Data Transformation Services covers all facets in the development of data transformations with DTS. The reader will learn how to create each of the DTS tasks, both in the User Interface supplied with SQL Server and through the object model using Visual Basic or VBScript.

Microsoft SQL Server 2000 Data Transformation Services is filled with numerous small examples, showing how each of the capabilities of DTS can be used. It also includes a number of Data Transformation Templates, which can be used by the readers to help develop their own packages.

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.

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.

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.

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.

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.

13. The Copy SQL Server Objects Task

When to Use the Copy SQL Server Objects Task. The Source and the Destination. Transfer Choices.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.