Brian Knight is the founder of Pragmatic Works and co-founder of [...] and [...].

Devin Knight is a SQL Server MVP, the Training Director at Pragmatic Works, and an author on six SQL Server books.

Jessica M. Moss is a well-known architect, speaker, author, and Microsoft MVP of SQL Server business intelligence who has created data warehousing solutions for a variety of industries.

Mike Davis, MCTS, MCITP, is the Managing Project Lead at Pragmatic Works, has co-authored several business intelligence books, and regularly speaks at SQL events.

Chris Rock is a software developer and program manager at Pragmatic Works, where he develops custom SSIS components and software.

Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

Introduction xxvii

Chapter 1: Welcome to SQL Server Integration Services 1

SQL Server SSIS Historical Overview 2

What's New in SSIS 2

Tools of the Trade 3

Import and Export Wizard 3

The SQL Server Data Tools Experience 4

SSIS Architecture 5

Packages 5

Control Flow 5

Data Flow 9

Variables 14

Parameters 14

Error Handling and Logging 14

Editions of SQL Server 14

Summary 15

Chapter 2: The SSIS Tools 17

Import and Export Wizard 17

SQL Server Data Tools 24

The Solution Explorer Window 26

The SSIS Toolbox 27

The Properties Windows 28

The SSIS Package Designer 28

Control Flow 29

Connection Managers 32

Variables 33

Data Flow 34

Parameters 35

Event Handlers 35

Package Explorer 36

Executing a Package 37

Management Studio 37

Summary 37

Chapter 3: SSIS Tasks 39

SSIS Task Objects 40

Using the Task Editor 40

The Task Editor Expressions Tab 41

Looping and Sequence Tasks 41

Script Task (.NET) 41

Analysis Services Tasks 44

Analysis Services Execute DDL Task 44

Analysis Services Processing Task 44

Data Mining Query Task 46

Data Flow Task 47

Data Preparation Tasks 48

Data Profiler 48

File System Task 50

Archiving a File 52

FTP Task 53

Getting a File Using FTP 54

Web Service Task 56

Retrieving Data Using the Web Service Task and XML Source Component 59

XML Task 62

Validating an XML File 64

RDBMS Server Tasks 66

Bulk Insert Task 66

Using the Bulk Insert Task 69

Execute SQL Task 71

Workflow Tasks 82

Execute Package Task 82

Execute Process Task 84

Message Queue Task 86

Send Mail Task 87

WMI Data Reader Task 88

WMI Event Watcher Task 91

Polling a Directory for the Delivery of a File 91

SMO Administration Tasks 92

Transfer Database Task 93

Transfer Error Messages Task 94

Transfer Logins Task 94

Transfer Master Stored Procedures Task 95

Transfer Jobs Task 96

Transfer SQL Server Objects Task 96

Summary 97

Chapter 4: The Data Flow 99

Understanding the Data Flow 99

Data Viewers 100

Sources 101

OLE DB Source 102

Excel Source 104

Flat File Source 105

Raw File Source 110

XML Source 110

[...] Source 111

Destinations 111

Excel Destination 112

Flat File Destination 112

OLE DB Destination 112

Raw File Destination 113

Recordset Destination 114

Data Mining Model Training 114

DataReader Destination 114

Dimension and Partition Processing 114

Common Transformations 115

Synchronous versus Asynchronous Transformations 115

Aggregate 115

Conditional Split 117

Data Conversion 118

Derived Column 119

Lookup 121

Row Count 121

Script Component 122

Slowly Changing Dimension 123

Sort 123

Union All 125

Other Transformations 126

Audit 126

Character Map 128

Copy Column 128

Data Mining Query 129

DQS Cleansing 130

Export Column 130

Fuzzy Lookup 132

Fuzzy Grouping 139

Import Column 143

Merge 145

Merge Join 146

Multicast 146

OLE DB Command 147

Percentage and Row Sampling 148

Pivot Transform 149

Unpivot 152

Term Extraction 154

Term Lookup 158

Data Flow Example 160

Summary 164

Chapter 5: Using Variables, Parameters, and Expressions 165

Dynamic Package Objects 166

Variable Overview 166

Parameter Overview 166

Expression Overview 167

Understanding Data Types 168

SSIS Data Types 168

Date and Time Type Support 170

How Wrong Data Types and Sizes Can Affect Performance 171

Unicode and Non-Unicode Conversion Issues 171

Casting in SSIS Expressions 173

Using Variables and Parameters 174

Defining Variables 174

Defining Parameters 175

Variable and Parameter Data Types 176

Working with Expressions 177

C#-Like? Close, but Not Completely 178

The Expression Builder 179

Syntax Basics 180

Using Expressions in SSIS Packages 194

Summary 204

Chapter 6: Containers 205

Task Host Containers 205

Sequence Containers 206

Groups 207

For Loop Container 207

Foreach Loop Container 210

Foreach File Enumerator Example 211

Foreach ADO Enumerator Example 213

Summary 218

Chapter 7: Joining Data 219

The Lookup Transformation 220

Using the Merge Join Transformation 221

Contrasting SSIS and the Relational Join 222

Lookup Features 224

Building the Basic Package 225

Using a Relational Join in the Source 227

Using the Merge Join Transformation 230

Using the Lookup Transformation 235

Full-Cache Mode 235

No-Cache Mode 239

Partial-Cache Mode 240

Multiple Outputs 243

Expressionable Properties 246

Cascaded Lookup Operations 247

Cache Connection Manager and Cache Transform 249

Summary 252

Chapter 8: Creating an End-to-End Package 253

Basic Transformation Tutorial 253

Creating Connections 254

Creating the Control Flow 257

Creating the Data Flow 257

Completing the Package 259

Saving the Package 260

Executing the Package 260

Typical Mainframe ETL with Data Scrubbing 261

Creating the Data Flow 263

Handling Dirty Data 263

Finalizing 268

Handling More Bad Data 269

Looping and the Dynamic Tasks 271

Looping 271

Making the Package Dynamic 272

Summary 274

Chapter 9: Scripting in SSIS 275

Introducing SSIS Scripting 276

Getting Started in SSIS Scripting 277

Selecting the Scripting Language 277

Using the VSTA Scripting IDE 278

Example: Hello World 279

Adding Code and Classes 281

Using Managed Assemblies 282

Example: Using Custom .NET Assemblies 283

Using the Script Task 286

Configuring the Script Task Editor 287

The Script Task Dts Object 288

Accessing Variables in the Script Task 289

Connecting to Data Sources in a Script Task 293

Raising an Event in a Script Task 303

Writing a Log Entry in a Script Task 309

Using the Script Component 310

Differences from a Script Task 310

Configuring the Script Component Editor 311

Accessing Variables in a Script Component 313

Connecting to Data Sources in a Script Component 314

Raising Events 314

Logging 315

Example: Data Validation 316

Synchronous versus Asynchronous 324

Essential Coding, Debugging, and Troubleshooting Techniques 327

Structured Exception Handling 327

Script Debugging and Troubleshooting 330

Summary 333

Chapter 10: Advanced Data Cleansing in SSIS 335

Advanced Derived Column Use 336

Text Parsing Example 338

Advanced Fuzzy Lookup and Fuzzy Grouping 340

Fuzzy Lookup 340

Fuzzy Grouping 347

DQS Cleansing 350

Data Quality Services 351

DQS Cleansing Transformation 355

Master Data Management 358

Master Data Services 359

Summary 362

Chapter 11: Incremental Loads in SSIS 363

Control Table Pattern 363

Querying the Control Table 364

Querying the Source Table 366

Updating the Control Table 366

SQL Server Change Data Capture 367

Benefits of SQL Server CDC 368

Preparing CDC 369

Capture Instance Tables 371

The CDC API 372

Using the SSIS CDC Tools 374

Summary 379

Chapter 12: Loading a Data Warehouse 381

Data Profiling 383

Initial Execution of the Data Profiling Task 383

Reviewing the Results of the Data Profiling Task 386

Turning Data Profile Results into Actionable ETL Steps 390

Data Extraction and Cleansing 391

Dimension Table Loading 391

Loading a Simple Dimension Table 392

Loading a Complex Dimension Table 397

Considerations and Alternatives to the SCD Transformation 408

Fact Table Loading 409

SSAS Processing 421

Using a Master ETL Package 426

Summary 428

Chapter 13: Using the Relational Engine 429

Data Extraction 430

SELECT * is Bad 430

WHERE is Your Friend 432

Transform during Extract 433

Many ANDs Make Light Work 437

SORT in the Database 437

Modularize 439

SQL Server Does Text Files Too 440

Using Set-Based Logic 444

Data Loading 446

Database Snapshots 446

The MERGE Operator 448

Summary 452

Chapter 14: Accessing Heterogeneous Data 453

Excel and Access 455

64-Bit Support 455

Working with Excel Files 457

Working with Access 462

Importing from Oracle 469

Oracle Client Setup 469

Importing Oracle Data 470

Using XML and Web Services 472

Configuring the Web Service Task 472

Working with XML Data as a Source 483

Flat Files 486

Loading Flat Files 487

Extracting Data from Flat Files 489

ODBC 491

Other Heterogeneous Sources 494

Summary 495

Chapter 15: Reliability and Scalability 497

Restarting Packages 498

Simple Control Flow 499

Containers within Containers and Checkpoints 501