SQL Server 2012 Integration Services 24-Hour

PREFACE xxv

Welcome to SSIS 1

Import and Export Wizard 1

SQL Server Data Tools 2

Architecture 2

Packages 4

Tasks 4

Data Flow Elements 5

Sources 5

Destinations 6

Transformations 6

SSIS Capabilities Available in Editions of SQL Server 2012 7

Summary 8

Section 1: Install ation and Getting Started

lesson 1: Moving Data with the Im port

and Export Wizard 11

Try It 14

Lesson Requirements 14

Hints 14

Step-by-Step 14

lesson 2: Install ing SQL Server Integration Services 17

lesson 3: Install ing the Sample Databases 21

Try It 22

Lesson Requirements 22

Hints 22

Step-by-Step 23

lesson 4: Creating a Solution and Project 25

Try It 26

Lesson Requirements 27

Hints 27

Step-by-Step 27

lesson 5: Exploring SQL Server Data Tools 29

The Solution Explorer 29

Deployment Models 31

The Properties Window 31

The Toolbox 32

The SS DT Design Environment 32

lesson 6: Creating Your First Package 35

Creating and Using Connection Managers 36

Using and Configuring Tasks 37

Exploring Package Encryption 38

Executing Packages 39

Try It 39

Lesson Requirements 39

Hints 39

Step-by-Step 39

lesson 7: Upgrading Packages to SQL Server 2012 41

Try It 43

Lesson Requirements 43

Hints 43

Step-by-Step 43

lesson 8: Upgrading to the Project Deployment Model 47

Try It 48

Lesson Requirements 49

Hints 49

Step-by-Step 49

Section 2: Control Fl ow

lesson 9: Using Precedence Constraints 59

Try It 61

Lesson Requirements 61

Hints 61

Step-by-Step 62

lesson 10: Manipulating Files with the File System Task 63

Try It 67

Lesson Requirements 67

Hints 67

Step-by-Step 67

lesson 11: Coding Custom Script Tasks 71

Try It 76

Lesson Requirements 76

Hints 76

Step-by-Step 76

lesson 12: Using the Execute SQL Task 79

Try It 85

Lesson Requirements 85

Hints 85

Step-by-Step 85

lesson 13: Using the Execute Process Task 87

Try It 89

Lesson Requirements 89

Hints 90

Step-by-Step 90

lesson 14: Using the Expression Task 93

Try It 95

Lesson Requirements 95

Hints 95

Step-by-Step 96

lesson 15: Using the Send Mail Task 99

Try It 103

Lesson Requirements 103

Hints 103

Step-by-Step 103

lesson 16: Using the FTP Task 107

Try It 110

Lesson Requirements 110

Hints 110

Step-by-Step 111

lesson 17: Creating a Data Fl ow 113

Try It 115

Lesson Requirements 115

Hints 115

Step-by-Step 115

lesson 18: Extracting Data from Sources 121

Source Assistant 121

OLE DB Source 122

Try It 125

Excel Source 128

Try It 129

Flat File Source 131

Try It 133

lesson 19: Loading Data to a Destination 139

Destination Assistant 140

OLE DB Destination 140

Try It 141

Flat File Destination 145

Try It 145

Excel Destination 147

Try It 147

lesson 20: Changing Data Types

with the Data Conversion Transform 151

Try It 154

Lesson Requirements 154

Hints 154

Step-by-Step 154

lesson 21: Creating and Replacing Columns with

the Derived Column Transform 159

Try It 163

Lesson Requirements 163

Hints 163

Step-by-Step 164

lesson 22: Roll ing Up Data with the Aggregate Transform 167

Try It 169

Lesson Requirements 169

Hints 169

Step-by-Step 169

lesson 23: Ordering Data with the Sort Transform 173

Try It 175

Lesson Requirements 176

Hints 176

Step-by-Step 176

lesson 24: Joining Data with the Lookup Transform 179

Cache Modes 179

Try It 181

The Cache Connection Manager and Transform 184

Try It 186

lesson 25: Auditing Data with the Row Count Transform 189

Try It 189

Lesson Requirements 190

Hints 190

Step-by-Step 190

lesson 26: Combining Multiple Inputs with

the Union All Transform 193

Try It 194

Lesson Requirements 194

Hints 194

Step-by-Step 195

lesson 27: Cl eansing Data with the

Script Component 197

Try It 199

Lesson Requirements 200

Hints 200

Step-by-Step 200

lesson 28: Separating Data with

the Conditional Split Transform 203

Try It 205

Lesson Requirements 205

Hints 206

Step-by-Step 206

Hints 257

Step-by-Step 257

lesson 35: Making a Task Dynamic with Expressions 261

Try It 263

Lesson Requirements 263

Hints 263

Step-by-Step 263

Section 5: Comm on ET L Scenarios

lesson 36: Loading Data Incrementall y 269

Try It 271

Lesson Requirements 272

Hints 272

Step-by-Step 272

lesson 37: Using the CDC Components in SSIS 281

CDC Control Task 283

CDC Source Task 284

CDC Splitter Task 286

Try It 286

Lesson Requirements 286

Hints 286

Step-by-Step 287

lesson 38: Using Data Quality Services 295

Try It 299

Lesson Requirements 300

Hints 300

Step-by-Step 300

lesson 39: Using the DQS Cl eansing Transform 309

Try It 312

Lesson Requirements 312

Hints 312

Step-by-Step 313

lesson 40: Creating a Master Package 317

Try It 319

Lesson Requirements 319

Hints 319

Step-by-Step 320

Section 6: Containers

lesson 41: Using Sequence Containers

to Organize a Package 327

Try It 328

Lesson Requirements 328

Hints 328

Step-by-Step 328

lesson 42: Using For Loop Containers to

Repeat Control Fl ow Tasks 331

Try It 332

Lesson Requirements 332

Hints 332

Step-by-Step 332

lesson 43: Using the Foreach Loop Container

to Loop Through a Coll ection of Objects 337

Try It 339

Lesson Requirements 339

Hints 339

Step-by-Step 339

Section 7: Configuring Packages

lesson 44: Easing Deployment with

Configuration Tables 347

Try It 354

Lesson Requirements 354

Hints 354

Step-by-Step 354

Final Deployment 356

lesson 45: Easing Deployment with Configuration Files 357

Try It 360

Lesson Requirements 361

Hints 361

Step-by-Step 361

Final Deployment 362

lesson 46: Configuring Child Packages 365

Configuring an Execute Package Task 366

Configuring a Child Package 368

Try It 369

Lesson Requirements 369

Hints 370

Step-by-Step 370

Section 8: Troubleshooting SSIS

lesson 47: Logging Package Data 375

Try It 379

Lesson Requirements 379

Hints 379

Step-by-Step 379

lesson 48: Using Event Handlers 381

Creating Event Handlers 382

Common Uses for Event Handlers 383

Try It 384

Lesson Requirements 384

Hints 384

Step-by-Step 384

lesson 49: Troubleshooting Errors 387

Working in the Progress Tab 388

Troubleshooting Steps 389

Try It 390

Lesson Requirements 390

Hints 390

Step-by-Step 390

lesson 50: Using Data Viewers 393

Try It 396

Lesson Requirements 396

Hints 396

Step-by-Step 396

lesson 51: Using Breakpoints 399

Try It 402

Lesson Requirements 402

Hints 402

Step-by-Step 402

Section 9: Administering SSIS

lesson 52: Creating and Configuring the SSIS Catalog 407

Creating the Catalog 407

Configuring the Catalog 408

Creating and Using Folders 409

Try It 409

Lesson Requirements 410

Hints 410

Step-by-Step 410

lesson 53: Deploying Packages to the Package Catalog 411

Using the Deployment Wizard 411

Deploying Packages in the Package Deployment Model 412

Try It 414

Lesson Requirements 414

Hints 414

Step-by-Step 414

lesson 54: Configuring the Packages 415

Creating Environments 415

Configuring the Package 417

Try It 419

Lesson Requirements 419

Hints 420

Step-by-Step 420

lesson 55: Configuring the Service 421

Try It 423

Lesson Requirements 423

Hints 423

Step-by-Step 423

lesson 56: Securing SSIS Packages 425

Securing Packages in the Package Deployment Model 425

Securing Packages in the Project Deployment Model 426

Try It 427

Lesson Requirements 427

Hints 427

Step-by-Step 428

lesson 57: Running SSIS Packages 431

Executing Packages in the Package Deployment Model 431

Running Packages in the Project Deployment Model 434

Try It 435

Lesson Requirements 436

Hints 436

Step-by-Step 436

lesson 58: Running Packages in T-SQL

and Debugging Packages 437

Running the Package 437

Debugging When Something Goes Wrong 439

Try It 440

Lesson Requirements 441

Hints 441

Step-by-Step 441

lesson 59: Scheduling Packages 443

Using Proxy Accounts 444

Try It 446

Lesson Requirements 446

Hints 446

Step-by-Step 446

lesson 60: Dimension Load 451

Try It 454

Lesson Requirements 454

Hints 454

Step-by-Step 454

lesson 61: Fact Table Load 459

Try It 460

Lesson Requirements 460

Hints 460

Step-by-Step 460

Section 11: Wrap Up and Review

lesson 62: Bringing It All Together 465

Lesson Requirements 465

Hints 466

Step-by-Step 467

appendix A: SSIS Component Crib Notes 473

When to Use Control Flow Tasks 473

When to Use Data Flow Transforms 474

appendix b: Problem and Solution Crib Notes 477

appendix c: What’s on the DVD ? 481

System Requirements 481

Using the DVD 481

What’s on the DVD 482

Troubleshooting 482

Customer Care 482

INDEX 483