DAT 332 SQL Server 2000 Data Transformation Services

DAT 332 SQL Server 2000 Data Transformation Services (DTS) Best Practices Euan Garden Product Unit Manager SQL Server Development Microsoft Corporation

Technologies Covered Data Transformation Services Analysis Services (Decision Support Objects) Transact SQL ADO Active. X Scripting

“Best Practices” Agenda Package Design Data Loading Advanced Techniques Auditing and Error Handling Analysis Services Management

Package Design Practices Drive Packages through Metadata Dynamic Properties Task Access to Property of any object Sources values from many locales Global Variables Building blocks for communicating metadata Populated by the Dynamic Properties Task UDL Files Configurable File for storing Connection Information

Package Design Practices Designing Modular Parent/Child Packages Execute Package Task Allows joining of Transaction Supports passing of Globals Execution through COM Automation Programmatic access to Package objects Eliminates Version Affinity

Parent Child Packages DTS Parent Child Package is Invoked by Active. X Script Can also be done with Execute Package Task DTS Child – Error Handler Example

Demo Design Practices Parent/Child Package(s) And UDL Files

“Best Practices” Agenda Package Design Data Loading Advanced Techniques Auditing and Error Handling Analysis Services Management

Data Loading Practices Leverage Fast Load Interface(s) Bulk Insert (Not always possible) Data Pump (For More Complex Logic) Characteristics of this Approach Bulk Insert Task Execute SQL Task Rowset Parameter ADO Disconnected Recordsets DTS Multiphase Data Pump

Data Loading Practices Benefits to this Approach Cache Rowset on Client (fast lookups) Hold updates on Client Conditional Inserts/Updates without Data Driven Query

Demo Design Loading Conditional Inserts And Updates With The Multiphase Data Pump

“Best Practices” Agenda Package Design Data Loading Advanced Techniques Auditing and Error Handling Analysis Services Management

Advanced Practices Multi-Threaded Data Loading Requires Multiple Connections Data is Loaded in Parallel Avoid Contention by Loading into Separate Entities

Advanced Practices Task Looping Requires at least two tasks (Executive and Trigger) Facilitated by Manipulating Workflow <Step>. Execution. Status = DTSStep. Exec. Stat_Waiting Maintain State in Global Variables

Advanced Practices Communication through Global Variables Only Means for Communicating among Tasks Examples Pointer to Position within Global Array Status of a Stored Procedure Call Flag to Shutdown Parallel Load Processes

Advanced Practices – Benefits ü ü ü Higher Utilization during Data Load Compressed Data Loading Window Easy Orchestration within DTS

Multi-Threaded Loading Task Marshall Initialization Task Thread 1 Loading Task Thread n Loading Task Thread 1 Restart Task Thread n Restart Task

Demo Advanced Practices Multi-Threaded Data File Loading

“Best Practices” Agenda Package Design Data Loading Advanced Techniques Auditing and Error Handling Analysis Services Management

Auditing And Error Handling Logging Options Logging to MSDB Logging to Text File Logging to Event Log Enabled through Package Properties Capture Step/Package Errors Enabled through Data Pump Properties Capture Erroneous Data

Auditing And Error Handling DTSPackage. Log Object Write. String. To. Log() Write. Task. Record() DTS Lineage Global Variables DTSLineage_Full DTSLineage_Short DTSError. Records Object Add() Clear()

Auditing And Error Handling Monitoring Execution – Simple as Writing SQL select package. name, package. description, stepname, step. errorcode, step. errordescription from msdb. . sysdtspackagelog package, msdb. . sysdtssteplog step where package. lineagefull = step. lineagefull and step. errorcode <> 0

Demo Error Handling Custom Logging Using Intrinsic DTS Options

“Best Practices” Agenda Package Design Data Loading Advanced Techniques Auditing and Error Handling Analysis Services Management

Analysis Services And DTS Analysis Services Processing Task Supports Processing of all OLAP Objects Customizable Processing through Tree. Key and other properties Prominent Properties Tree. Key Server. NameDBNameCube. FolderCube. Name Item. Type 1=OLAP DB, 4=Cube, 7=Partition and 9=Dimension Processing. Option 0=Full Process, 1=Refresh Data and 2=Incremental Update

Analysis Services And DTS Decision Support Objects Provide access to the AS Management Interfaces Automated via Ax. Script Supports Partition Management Creating Cloning Dropping Merging of partitions Should be an OLAP Administrator to Use

DSO Hierarchy

Analysis Services And DTS Benefits Opportunity to Dynamically Manage Partitions and other Objects Support for Dynamic Processing Inclusion in the BI Solution Load

Demo Analysis Services Dynamic Partition Creation And Processing

Community Resources DTS Whitepaper – Search on MSDN for “Using DTS for Business Intelligence” Microsoft Site www. microsoft. com/sql SQLDTS Site www. sqldts. com SQL Server Developers Network www. sqldev. net SQLServer Central www. sqlservercentral. com

Ask The Experts Get Your Questions Answered I’ll be in the Experts booth right after this session

Questions? euang@microsoft. com

Community Resources http: //www. microsoft. com/communities/default. mspx Most Valuable Professional (MVP) http: //www. mvp. support. microsoft. com/ Newsgroups Converse online with Microsoft Newsgroups, including Worldwide http: //www. microsoft. com/communities/newsgroups/default. mspx User Groups Meet and learn with your peers http: //www. microsoft. com/communities/usergroups/default. mspx

evaluations

© 2003 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.