SQL Server Integration Services Design Patterns

SQL

Server

_Integration

Services

_Design

Patterns

Second Edition

Andy

Leonard

Tim Mitchell

Matt

Masson

Jessica Moss

Michelle Ufford

Apress*

Contents

J

First-Edition Foreword xv

About the Authors xvii

About theTechnical Reviewer xix

Chapter

1: Metadata Collection 1

About SQL Server Data Tools 1

A Peek at the Final Product 1

SQLServer

_{Metadatacatalog}

3 sys.dm_os_performance_counters 3 sys.dm_db_index__usage_stats 3 sys.dm_os_sys_info 3 sys.tables 3 sys.indexes 3 sys.partitions 4 sys.allocation_units 4

Setting Up

the Central

_Repository

4

The Iterative Framework 6

Metadata Collection 14

Summary

26

HChapter

2:Execution Patterns 27

Building

the Demonstration SSIS

_Package

27

DebugExecution 28

Command-LineExecution 29

ExecutePackage Utility 30

TheSQLServer 2014

_Integration

Services Service 30 IntegrationServices_Catalogs 30

IntegrationServer_CatalogStored Procedures 31

Scheduling

SSIS

_Package

Execution 53 SchedulinganSSIS _Package 53 SchedulingaFile_{System Package} 54

RunningSQLServer_AgentJobs with the Custom Execution Framework 55

Runningthe Custom ExecutionFrameworkwith SQL Server_Agent 56 Execute_PackageTask 57

Execution from

_Managed

Code 58

The Demo_Application 58

ThefrmMain Form 59

Conclusion 70

^Chapter

3:

_Scripting

Patterns 71

The Toolset 71

Should IUse_Script? 72

TheScriptEditor 72

The_ScriptTask 75

The_{Script Component} 77

Script

Maintenance Patterns 78

Code Reuse 78

Source Control 79

Scripting Design

Patterns 79

Connection_Managersand_Scripting 80

Variables 82

NamingPatterns 85

CONTENTS

Chapter

4:SQLServer Source Patterns 87

Setting Up

a Source 87

Selecting

aSQL Server Connection

Manager

and Provider 88

ADO.NET 89

ODBC 89

OLE DB 91

Creating

aSQL Server Source

Component

92

Writing

a SQL Server Source

Component Query

95

ADO.NETData Access 95

OLE DB Data Access 96

WasteNot,Want Not 97

Data Translations 97

Source Assistant 97

Summary

99

Chapter

5: Data Correction with Data

_Quality

Services 101

OverviewofData

_Quality

Services 101 Usingthe Data_QualityClient 102

Using

DQS withSSIS 108

DQSCleansingTransform 108

DQS ExtensionsonCodePlex 113

Cleansing

Data in the Data Flow 114 HandlingtheOutputof theDQS CleansingTransform 114

Performance Considerations 117

Approving

and

_Importing

Cleansing

Rules 121

Conclusion 123

^Chapter

6: DB2 Source Patterns 125 DB2 Database

_Family

125

Selecting

a DB2 Provider 126

Find theDatabase Version 126

Pick ProviderVendor 127

Connecting

to aDB2 Database 127

Querying

the DB2 Database 130

DB2 Source_ComponentParameters 131

DB2 Source_{Component Dynamic}Queries 132

Summary

133

Chapter

7: Flat File Source Patterns ...135

Flat File Sources 135 Movingto SSIS! 136

Strong-Typingthe Data 138

Introducinga_Data-Staging Pattern 140

Variable-Length

Rows 143

Readingintoa Data Flow 144

Splitting Record_Types 145

Terminatingthe Streams 146

Header and Footer Rows 147 ConsumingaFooter Row 148

ConsumingaHeader Row 150

Producing aFooter Row 152

Producing aHeaderRow 159

The Archive File Pattern 163

Summary

169

Chapter

8:

_Loading

a PDW

_Region

in APS 171

Massively

Parallel

_Processing

171

APS

_Appliance

Overview 172

Hardware Architecture 172

Software Architecture 173

Shared-NothingArchitecture 175

Clustered Columnstore Indexes 175

CONTENTS

Loading

Data 176

DWLoadervs.IntegrationServices 176

ETLvs. ELT 177

Data

_Import

Pattern for PDW 178

Prerequisites 178

Preparingthe Data 179

PackageOverview 181

The Data Source 181

The Data Transformation 183

The DataDestination 184

Multithreading 189

Limitations 190

Summary

191

Chapter

9:XML Patterns 193

Using

the XML Source 193

Dealingwith_{Multiple Outputs 194}

MakingThingsEasier with XSLT 200

Using

a

_{Script Component}

₂₀₃

Configuringthe_{Script Component 203} ProcessingXML with XmlSerializer 209

ProcessingXMLwith XmlReader and LINQ to XML 210

Conclusion 212

Chapter

10:

_{Expression Language}

Patterns 213

Getting

to Know the

_{Expression Language}

213

WhatIs the _{Expression Language?} 213

WhyUse Expressions? 214

LanguageEssentials ₂₁₅

Limitations 215

Putting

the

_Expression

_Language

to Work 216

Package Expressions 216

Variable_Expressions 217

ConnectionManagers 217

Project-LevelConnection_Managers 219

Control Flow 219

Data Flow Expressions 222

Conclusion 226

aChapter

11: Data Warehouse Patterns • 227

Incremental Loads 227

WhatIsanIncrementalLoad? 227

Why Incremental Loads? 228

The_{Slowly Changing} Dimension ₂₂₈ Incremental Loads of Fact Data 228

Incremental Loads in SSIS 228

Native SSIS_{Components 229}

The_Slowly

_Changing

Dimension Wizard 232

TheMERGE Statement ₂₃₄

Change Data_{Capture (CDC) 237}

DataErrors ₂₄₂

SimpleErrors ₂₄₂

MissingData ₂₄₃

Codingto Allow Errors ₂₄₆

Data Warehouse ETLWorkflow ₂₄₈ Dividing Upthe Work ₂₄₈ One_Package=One Unit of Work ₂₄₉

Conclusion ₂₅₀

CONTENTS

Chapter

12: OData Source -251

Understanding

the OData Protocol 251

Data_{Type Mappings} 252

Query Options 253

Configuring

the OData Connection

_Manager

254

Enabling

Microsoft Online ServicesAuthentication 254

Configuring

the Source

_Component

256

Overriding

Data

_Types

259

Conclusion 260

a*

_Chapter

13:

_{Slowly Changing}

Dimensions 261 The

_Slowly

_Changing

Dimension Transform 261

Runningthe Wizard 262

Usingthe Transformations 267

OptimizingPerformance 268

Third-Party

SCD

_Components

269

Merge

Pattern 270

Handling Type 1 Changes 271

Handling Type2_Changes 272

Conclusion ₂₇₂

HChapter

14:

_Loading

the Cloud 275

Interacting

with the Cloud 275

Incremental Loads to Azure SQL Database 276

ChangeDetection 276

New Rows_(Only) 276

Building

the Cloud Loader 277

Conclusion 280

Chapter

15:

Logging

and

Reporting

Patterns 281

Package

Logging

and

_Reporting

281

Setting Up PackageLogging 281 Reportingon_{Package Logging} 282

DesignPattern:PackageExecutions 283

Catalog Logging

and

_Reporting

283

Setting UpCatalog Logging 283 CatalogTables 285

ChangingLoggingLevels After the Fact 286

Design

Patterns 287

Changingthe_LoggingLevel 287

UsingtheExistingReports 289 CreatingNew_Reports 290

Summary

291

Chapter

16: Parent-Child Patterns 293 Master

_Package

Pattern 293 AssigntheChild_Package 294 ConfigureParameterBinding 295

Dynamic

Child

Package

Pattern 296

Child-to-Parent Variable Pattern 302

Conclusion 303

Chapter

17:

_{Configuration}

305

Parameters 305

ConfiguringYour_PackageUsingParameters 307

Usingthe Parametrize Dialog 309

CreatingVisual StudioConfigurations 310

SpecifyingEntry-PointPackages 312

Connection

_Managers

313

CONTENTS

Parameter

_{Configuration}

onthe Server 313 Default_{Configuration} 314

Server Environments 315

DefaultParameterValues_UsingT-SQL 317

Package Execution_Throughthe SSIS_Catalog 317

Parameterswith DTEXEC 320

Projectsonthe FileSystem 320

Projectsin the SSIS_Catalog 321

Dynamic Configurations

322

ConfiguringfromaDatabase Table 323

SettingValuesUsinga_ScriptTask 326

DynamicPackageExecutions 327

Conclusion 329

Chapter

18:

_Deployment

331

Project Deployment

Model 331

SSIS

_Catalog

332

Deployment

Methods 334

DeploymentfromtheCommandLine 335

Deployment Using Custom Code 336

Deployment UsingPowerShell 337

Deployment UsingSQL 338

Package

Deployment

Model 339

Conclusion 341

Chapter

19: Business

_{Intelligence Markup Language}

343 A Brief

_History

of Business

_Intelligence

_{Markup Language}

343

Building

Your First Biml File 344

Building

aBasic Incremental Load SSIS

Package

347

CreatingDatabasesandTables 347

Adding Metadata 349

SpecifyingaData Flow Task 350

AddingTransforms 350

Testingthe Biml 356

Using

Bimlas an SSIS

Design

Patterns

Engine

360

Time for aTest 367

Conclusion 368

HChapter

20: Biml andSSIS Frameworks 369

Using

Biml with an SSIS Framework 369

AddingSSIS_PackageMetadatato theFramework 369

Executingthe Biml File 374

Generating

the SSIS Command-Line 375

Summarizing

376

£9Appendix

A: Evolution ofan SSIS Framework 377

Starting

in the Middle 377

IntroducingSSIS_Applications 387

A Note About_{Relationships} 389

RetrievingSSISApplicationsinT-SQL 392

RetrievingSSIS_Applicationsin SSIS 396

Monitoring

Execution 399

Building ApplicationInstance_Logging 399

Building Package Instance _Logging 406

BuildingError_Logging 410

Reporting

Execution Metrics 420

Conclusion 434

Index 435