book

SQL Server DTS

by Steve Hughes,, Steve Miller,, Jim Samuelson,, Marcelino Santos,, Brian Sullivan

August 2001

Intermediate to advanced

512 pages

10h 31m

English

Sams

Read now

Unlock full access

Copyright
About the Authors
About the Technical Reviewers
Acknowledgments
Tell Us What You Think
Preface
Introduction
Scope and AudienceOrganization and LayoutPart I, “Data Transformation Services—The Tool”Part II, “Automating Data Transformation Services”Part III, “DTS and the Business Intelligence Process”Part IV, “Using the Tool—Case Studies”ConventionsHow to Use This Book
I. Data Transformation Services—The Tool
1. DTS in the ETL World
DTS and Traditional TechnologiesBCPReplicationTransact-SQL (T-SQL)DTS and Today’s TechnologiesDTS and Commerce ServerDTS and BizTalk ServerReal-World Scenarios Used in This Book—About Sparks ElectronicsSummary
2. DTS—An Overview
The DTS PackageBuilding a PackageThe ScenarioPackage DesignStep 1: Create the Basic PackageStep 2: Create the Data ConnectionsStep 3: Create Global VariablesStep 4: Prepare Spreadsheet and Extract Reporting DateStep 5: Load Actuals DataStep 6: Load Variance Report DataStep 7: Create Error MessageStep 8: Add WorkflowSummary

3. Connections
Database Management System ConnectionsMicrosoft OLE DB Provider for SQL ServerMicrosoft ODBC Driver for OracleFile System Database ConnectionsApplication ConnectionsText File ConnectionsText File (Source and Destination)HTML File (Source)Microsoft Data LinksUDL Link NameData Link PropertiesOther ConnectionsEditing ConnectionsSummary
4. DTS Tasks
Using DTS TasksUsing the Transform Data TaskSetting up the Source DataUsing the Query DesignerUsing Parameters in the QuerySetting up the Destination TableTransformationsUnderstanding the Built-In Transformation TypesUnderstanding the Transformation FlagsThe ActiveX Script Property WindowTransform Status CodesMapping Transformation ColumnsLookupsTransform Data Task Properties OptionsData Driven Query TaskUnderstanding the Role of the Binding TableThe Driven QueriesData Driven Query Task OptionsThe Multiphase Data PumpGaining Access to the Multiphase Data PumpUnderstanding the Data Pump PhasesBulk Insert TaskBulk Insert Task OptionsSummary
5. More DTS Tasks
Execute SQL TaskUsing the SQL Result SetsSaving a RowSaving an Entire Result SetActiveX Script TaskExecute Process TaskExecute Package TaskInner Package Global VariablesOuter Package Global VariablesDynamic Properties TaskSend Mail TaskSending MailConfiguring Mail Services for SQL ServerFile Transfer Protocol TaskMessage Queue TaskSending MessagesReceiving MessagesCopy SQL Server Objects TaskTransfer Databases TaskTransfer Logins TaskTransfer Master Stored Procedures TaskTransfer Jobs TaskTransfer Error Messages TaskAnalysis Services Processing TaskProcessing OptionsProcessing CubesProcessing Mining ModelsData Mining TaskSummary
6. DTS Workflows
How to Use DTS WorkflowsPrecedence PropertiesOn CompletionOn SuccessOn FailureWorkflow PropertiesTransaction PropertiesExecution PropertiesExecute on Main ThreadClose Connection on CompletionFail Package on Step FailureDSO Rowset ProviderQuerying Package DataUsing OPENROWSET to Query Package DataUsing Distributed Queries to Query Package DataDisable This StepTask PriorityActiveX ScriptSetting up the Sample PackageStopping ExecutionRestarting ExecutionLooping ExecutionSummary
7. Package
General PropertiesGlobal VariablesLoggingError HandlingTransactionsTransaction ScenariosSequential TasksParallel TasksBranching WorkflowsCheckpointing for Multiple TransactionsInheriting TransactionsMiscellaneous PropertiesLineageScanning OptionsOLE DBDisconnected EditPackage Storage LocationsSQL ServerMeta Data ServicesStructured Storage FileVisual Basic FilePackage SecurityPackage ExecutionPackage SchedulingPackage DeletionSummary
8. Putting It All Together—An Extended DTS Example
Setting the ScenarioSolutionDevelopment EnvironmentGlobal, Local Configuration, and Transactional DataBuilding the Reporting Database and Creating Data Extract PackagesBuilding the Reporting DatabaseBuilding a Package to Import Configuration Data to tempdbBuilding a Package to Import Site Data to tempdbMoving Data from tempdb into the Reporting DatabaseManipulating Global Configuration DataManipulating Local Configuration DataManipulating Large Data SetsExporting Data and Generating ReportsCreating an Excel spreadsheetSimple HTML ReportUsing HTML to Report Job HistoryDTS2000Mfg_Production—Pulling It All TogetherScheduling the DTS PackageTracking Job HistoryControlling Scheduled DTS JobsEnhancing PerformanceGeneral Server PerformanceAvoid Using DTSUsing Bulk Insert and BCPAvoid ExecuteOnMainThreadAvoid ActiveX ScriptsEnhancing Data-Pump PerformanceData-Driven QueriesSummary
II. Automating Data Transformation Services
9. Building a Package Using Visual Basic
The ScenarioGenerating a VB DTS Package with SQL 2000 DTS DesignerIncluding the DTS Script in a VB ApplicationDTS Dynamic Link LibrariesVB Threading ConsiderationsConnection ConsiderationsCompiling and Running the PackageThe DTS Object ModelThe DTS VB ScriptSetting Package OptionsDefining the Package’s GlobalVariables CollectionDefining Package ConnectionsDefining Package StepsDefining Package ConstraintsDefining Package TasksExecuting the PackageAdding an ExecuteSQLTaskAdding an ActiveScriptTaskAdding a DataPumpTaskDefining the DataPumpTask OptionsDefining the DataPumpTask’s Transformation ScriptDefining the DataPumpTask’s Transformation Script ColumnsDefining the DataPumpTask’s Transformation Script ActiveX ScriptDefining the DataPumpTask’s Transformation Script LookupSaving the DataPumpTask to the Package’s Tasks CollectionExecuteSQLTaskBuilding Your SolutionModifying the VB ApplicationRunning the ApplicationViewing the OutputSummary
10. Interacting with a Package Using Visual Basic
Executing DTS Packages in Visual BasicMaking Best-Practice Upgrades to the VB .bas FilesLoading and Saving Packages in Visual BasicPackage Load and Save Repository MethodsPackage Load and Save SQL Server MethodsPackage Load and Save Storage File MethodsHandling DTS Package Events in Visual BasicOnStart EventOnFinish EventOnProgress EventOnQueryCancel EventOnError EventHandling DTS Package Errors in Visual BasicProcessing Multiple Input FilesScenarioSolutionProcessing Multiple SpreadsheetsScenarioSolutionLoading XML via DTSScenarioSolutionProcessing Outlook via DTSScenarioSolutionSummary
III. DTS and the Business Intelligence Process
11. Introduction to Data Warehousing and Analysis Services
Data Warehouse Versus Operational Transaction-Oriented DatabaseDimensional ModelingBusiness ModelingData MartsThe Operational Data StoreStaging AreaDimensional Modeling of Business ProcessDimension Table OverviewDimension GranularitySlowly Changing DimensionsFact Table OverviewMeasuresDimension Foreign KeysAssembling the PiecesPopulating the Data MartCreating the Data Staging AreaCleansing and Transforming DataIntroduction to Analysis ServicesAnalysis Services ArchitectureAnalysis Services DatabaseCubesDimensionsDimension LevelsParent-Child DimensionsDimension MembersMember PropertiesMeasures and AggregationsCalculated MembersSummary
12. Building a Data Warehouse with DTS and Analysis Services
The Data Warehouse ProcessStep 1: Create the Connections to the Data SourcesStep 2: Define a Connection to the Data MartStep 3: Define the Queries to Extract Data from the Sources Previously DefinedStep 4: Define the Transformation and Load StepsBuilding a Cube with Analysis ServicesSetting up a Data SourceCreating a CubeSummary
13. DTS and Data Mining
Analyzing DataData Mining ModelDecision TreesClusteringData Mining Model ContentData Mining ExerciseLet the Analysis BeginSummary
IV. Using the Tool— Case Studies
14. Custom Error Logging with DTS
Solution DesignImplementationCreate Logging Table in SQL ServerCreate Connection to Logging TableChange the Shipping Budget Excel Connection to Shipping Budget ErrorCreate the Lookup Query to Insert the Log TransactionsAdd Error-Handling Code to the Transform Data ScriptNotifying the Administrator of ErrorsExecute PackageError-Logging Design Strengths and WeaknessesCustom Logging Using Execute SQL and Execute Scripting TasksExecute SQL TaskExecute Script TaskSummary
15. Managing Distributed Databases with DTS and Message Queues
Example DesignImplementationStep 1: Create QueueStep 2: Create the Recipient DatabaseStep 3: Create the Sending Data PackageStep 4: Create an ActiveX Script TaskStep 5: Send the Message with the XML String to the Message QueueStep 6: Make the Package Loop to Create Many RecordsCreate Package to Receive and Process the MessagesStep 1: Create the Stored ProcedureStep 2: Create a New PackageStep 3: Add ConnectionsStep 4: Add a Transform Data ConnectionStep 5: Create a Looping MechanismStep 6: Add Workflow.Summary
16. Creating Your Own Custom Task
Setting up a Custom Task-Development ProjectThe Custom Task InterfaceThe Description PropertyThe Name PropertyThe Properties CollectionThe Execute MethodThe pPackage ParameterThe pPackageEvents ParameterThe pPackageLog ParameterThe pTaskResult ParameterGenerate HTML Task: Execute MethodCreating the Property PageThe Custom Task UI InterfaceThe Initialize MethodThe New MethodThe Edit MethodThe Delete MethodThe Help MethodThe GetUIInfo MethodThe CreateCustomToolTip MethodAdding a Custom Icon to the Custom TaskRegistering the Custom TaskUsing the Custom TaskSummary
17. Executing a DTS Package from a Stored Procedure
Executing DTS Packages from T-SQLExecuting DTS Packages with xp_cmdshellUsing dtsrunuiExecuting DTS Packages with OLE Automationsp_OACreatesp_OAMethodsp_OADestroysp_displayoaerrorinfoPutting It TogetherOLE Automation Versus xp_cmdshellSummary
18. Data Lineage
Introduction to Microsoft’s Meta Data ServicesMeta Data Services and DTSPackage Property SettingsScanning OptionsSaving DTS Packages as Meta DataViewing DTS Packages as Meta DataUsing the Meta Data BrowserHow to Use Data LineageRow-Level Data LineageColumn-Level Data LineageData Lineage Applied to a PackageOverview of the PackageSteps to Set Up Meta DataViewing the Data LineageSummary

Content preview from SQL Server DTS

Chapter 8. Putting It All Together—An Extended DTS Example

Chapter 1, “DTS in the ETL World”, introduced Sparks Electronics, an electronics manufacturer and distributor with offices in several states. Chapter 2, “DTS—An Overview,” examined basic package components, and you saw how easy it is to create a stand-alone package that could be run on demand. Chapters 3 through 7 took a more in-depth look at DTS package components.

In this chapter, you get a chance to put all this knowledge together and build an automated system to collect and distribute reporting data for Sparks Electronics. In addition to building the package, you will examine issues ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Extending SSIS with .NET Scripting: A Toolkit for SQL Server Integration Services

Publisher Resources

ISBN: 0735711232Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL Server DTS

by Steve Hughes,, Steve Miller,, Jim Samuelson,, Marcelino Santos,, Brian Sullivan

Chapter 8. Putting It All Together—An Extended DTS Example

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.