Microsoft SQL Server 2012 Integration Services

Foreword......Page 23
Introduction......Page 25
Part I: Overview......Page 31
 Chapter 1: SSIS Overview......Page 33
   Consolidation of Data from Heterogeneous Data Sources......Page 34
   Movement of Data Between Systems......Page 39
   Loading a Data Warehouse......Page 42
   Cleaning, Formatting, or Standardization of Data......Page 46
   Identification, Capture, and Processing of Data Changes......Page 47
   Coordination of Data Maintenance, Processing, or Analysis......Page 48
  Evolution of SSIS......Page 50
  Setting Up SSIS......Page 51
   SQL Server Features Needed for Data Integration......Page 52
   SQL Server Editions and Integration Services Features......Page 54
  Summary......Page 55
 Chapter 2: Understanding SSIS Concepts......Page 57
   Tasks......Page 58
   Precedence Constraints......Page 60
   Variables and Expressions......Page 61
   Containers......Page 62
   Connection Managers......Page 65
   Packages and Projects......Page 66
   Parameters......Page 67
   Log Providers......Page 68
   Event Handlers......Page 70
   Source Adapters......Page 71
   Destination Adapters......Page 72
   Transforms......Page 73
  SSIS Catalog......Page 74
   Overview......Page 75
   Environments......Page 76
  Summary......Page 77
  What’s New in SSIS 2012......Page 79
   Feature Changes in SSIS......Page 80
   Upgrade Requirements......Page 82
   Upgrade Scenarios......Page 83
   Unsupported Upgrade Scenarios......Page 84
   Upgrade Advisor......Page 85
   Performing Upgrade......Page 91
   Addressing Upgrade Issues and Manual Upgrade Steps......Page 99
   Conversion to Projects after Upgrade......Page 101
  Summary......Page 109
Part II: Development......Page 111
   Visual Studio......Page 113
   Undo and Redo......Page 114
   Toolbox......Page 115
   Variables Window......Page 117
   Zoom Control......Page 118
   Status Icons......Page 119
   Solution Explorer Changes......Page 120
   Visual Studio Configurations......Page 122
   Project Compilation......Page 123
   Deployment Wizard......Page 124
   Project Conversion Wizard......Page 125
   Change Data Capture......Page 126
   Expression Task......Page 129
   Expression Adorners......Page 130
   Execute SQL Task......Page 131
   Connection Assistants......Page 132
   Improved Column Mapping......Page 133
   Grouping......Page 134
   Row Count and Pivot Transform User Interfaces......Page 135
   Flat File Source Changes......Page 136
   Visual Studio Tools for Applications......Page 138
   Script Component Debugging......Page 139
   .NET 4 Framework Support......Page 141
   New Expression Functions......Page 142
  Summary......Page 143
   Package Format Changes......Page 145
   Visual Studio Configurations......Page 146
   Connecting to Team Foundation Server......Page 147
   Adding an SSIS Project to Team Foundation Server......Page 150
   Change Management......Page 154
   Changes to the SSIS Visual Studio Project File......Page 157
   One Developer Per Package......Page 159
  Summary......Page 160
   Package Deployment Model......Page 161
   Project Deployment Model......Page 163
   Creating an SSIS Project......Page 166
   Designing an Integration Services Data Flow......Page 177
   Using Parameters and the ForEach Container......Page 182
   Using the Execute Package Task......Page 186
   Building and Deploying an Integration Services Project......Page 189
  Summary......Page 190
  Previous Connectivity Options in SSIS......Page 191
   Providers for Connectivity Technology......Page 192
   OLE DB, ADO.NET, and ODBC......Page 194
  New Connectivity Options in SSIS 2012......Page 195
   Introducing ODBC......Page 196
   ODBC Components for SSIS......Page 198
   ODBC Source......Page 199
   ODBC Destination......Page 204
   64-Bit and SSIS......Page 207
   SSIS Tools on 64-Bit Architecture......Page 208
   Connecting to Microsoft Excel and Access......Page 214
   Connecting to Oracle......Page 216
   Creating Custom Components......Page 219
   Using Script Components......Page 221
  Summary......Page 223
  CDC in SQL Server......Page 225
   Using CDC in SQL Server......Page 226
   CDC Scenarios in ETLs......Page 227
   Stages in CDC......Page 228
   CDC State......Page 232
   CDC Control Task......Page 235
   Data Flow Component: CDC Source......Page 241
   CDC Splitter Component......Page 245
   Introduction......Page 247
   CDC Service Configuration MMC......Page 249
   Oracle CDC Designer MMC......Page 251
   MSXDBCDC Database......Page 263
   Oracle CDC Service Executable (xdbcdcsvc.exe)......Page 265
   Data Type Handling......Page 268
  Summary......Page 270
  Data Profiling Task......Page 271
  Fuzzy Lookup Transformation......Page 276
  Fuzzy Grouping Transformation......Page 281
  Data Quality Services Cleansing Transform......Page 284
  Summary......Page 291
Part III: Database Admin......Page 293
 Chapter 10: Configuration in SSIS......Page 295
   What to Configure......Page 296
  Configuration in SSIS 2012......Page 297
   Creating Package Parameters......Page 298
   Creating Project Parameters......Page 301
   API for Creating Parameters......Page 303
   Using Parameters......Page 304
   Configuration Through SSMS......Page 311
   Configuration Using SQL Agent, DTExec, and T-SQL......Page 316
   SSIS Environments......Page 317
  Package Deployment Model and Backward Compatibility......Page 321
   Package Deployment Model......Page 322
   Best Practices with Package Deployment Model......Page 325
   Best Practices with Project Deployment Model......Page 328
  Summary......Page 330
  Ways to Run SSIS Packages......Page 331
   Package Locations......Page 333
   Configuring Packages......Page 337
   Error Dumps......Page 338
   Logging Options......Page 339
  Running Packages in the SSIS Catalog......Page 341
   Prepare Executions......Page 342
   Starting SSIS Package Executions......Page 346
   View Executions......Page 349
   Executions with T-SQL......Page 350
  Running Packages from SQL Agent......Page 351
   Create an SSIS Job Step......Page 352
   Execute Packages from the SSIS Catalog......Page 353
  Running Packages via PowerShell......Page 355
  Creating and Running SSIS Packages Programmatically......Page 356
  Summary......Page 361
  Overview of SSIS Stored Procedures and Views......Page 363
   SSIS Catalog Properties......Page 364
   Setting SSIS Catalog Properties......Page 365
   Deploy an SSIS Project to the SSIS Catalog......Page 366
   Learning About the SSIS Projects Deployed to the SSIS Catalog......Page 367
   Configuring SSIS Projects......Page 368
   Managing SSIS Projects in the SSIS Catalog......Page 371
   Running SSIS Packages in the SSIS Catalog......Page 373
  SSIS Environments......Page 377
   Creating SSIS Environment Variables......Page 378
   Configuring SSIS Projects Using SSIS Environments......Page 379
   Configuring SSIS Projects Using Reference Values......Page 380
   Managing SSIS Environment and Environment Variables......Page 381
  Summary......Page 383
  PowerShell Refresher......Page 385
  PowerShell and SQL Server......Page 386
   SSIS Management Object Model......Page 389
   PowerShell with SSIS Management Object Model......Page 390
   PowerShell and SSIS Using T-SQL......Page 394
  Summary......Page 396
  Getting Started with SSIS Reports......Page 397
  Data Preparation......Page 399
   Integration Services Dashboard......Page 400
   All Executions Report......Page 402
   All Validations and All Operations Reports......Page 403
  Using SSIS Reports to Troubleshoot SSIS Package Execution......Page 405
  Using the Execution Performance Report to Identify Performance Trends......Page 410
  Summary......Page 413
Part IV: Deep-Dive......Page 415
   Overview......Page 417
   Load......Page 418
   Validate......Page 420
   Execute......Page 422
  The Data Flow Engine......Page 429
   Overview......Page 430
   Execution Control......Page 433
   Backpressure......Page 440
   Engine Tuning......Page 443
  Summary......Page 446
   Creating the SSIS Catalog......Page 447
   Unit of Deployment to the SSIS Catalog......Page 449
   What Is Inside SSISDB?......Page 450
   SQL Server Instance Starts Up......Page 452
   SSIS Catalog and Logging Levels......Page 454
  Understanding the SSIS Package Execution Life Cycle......Page 455
   Using the Windows Application Event Log......Page 458
  SSIS Catalog Maintenance and SQL Server Agent Jobs......Page 459
  Backup and Restore of the SSIS Catalog......Page 462
   Back Up SSISDB......Page 463
   Restore SSISDB......Page 464
  Summary......Page 466
   Control Package Access......Page 467
   Package Encryption......Page 471
   Sensitive Variables and Parameters......Page 473
   Package Signing......Page 474
  Security in the SSIS Catalog......Page 475
   Security Overview......Page 476
   Manage Permissions......Page 478
   DDL Trigger......Page 485
   Create Credentials......Page 486
   Create Proxy Accounts......Page 488
   Create SQL Agent Jobs......Page 491
  Summary......Page 493
  Configure Logging Options......Page 495
   Choose Containers......Page 496
   Select Events......Page 498
   Add Log Providers......Page 500
   SQL Server......Page 503
   Windows Event Log......Page 504
   XML Files......Page 505
   Logging Levels......Page 506
   Event Logs......Page 508
   Event Context Information......Page 509
   Customizing Logging Fields......Page 510
   Developing Custom Log Providers......Page 511
  Summary......Page 513
   Programmatic Generation of SSIS Packages......Page 515
   Metadata-Driven Package Execution......Page 516
  Dynamic Package Generation......Page 517
   Handling Design-Time Events......Page 518
   Samples......Page 520
  Metadata-Based Execution......Page 529
   Custom Package Runner......Page 530
   Using PowerShell with the SSIS Management Object Model......Page 534
   Using PowerShell with SQL Agent......Page 537
   Samples on Codeplex......Page 540
   Third-Party Solutions......Page 541
  Summary......Page 545
Part V: Troubleshooting......Page 547
  Getting Started with Troubleshooting......Page 549
  Data Preparation......Page 551
  Troubleshooting Failures of SSIS Package Executions......Page 552
   Three Key Steps Toward Troubleshooting Failures of SSIS Package Executions......Page 554
   Finding the Root Cause of Failure......Page 558
  Troubleshooting the Execute Package Task and Child Package Executions......Page 561
   DiagnosticEx Events......Page 563
   Execute Package Task and Execution Path......Page 564
  Troubleshooting SSIS Package Execution Failures Scheduled with SQL Agent......Page 566
   Using SQL Agent History Tables to Determine the SSIS Job Steps That Failed......Page 569
  Summary......Page 570
 Chapter 21: SSIS Performance Best Practices......Page 571
   OVAL Technique......Page 572
   Measuring System Performance......Page 574
   Measuring Performance of Data Flow Tasks......Page 578
   Parallelize Your Design......Page 584
   Using SQL Server Optimization Techniques......Page 588
   Bulk Loading Your Data......Page 590
   Keeping SSIS Operations in Memory......Page 593
   Optimizing SSIS Lookup Caching......Page 594
   Optimizing SSIS Infrastructure......Page 598
  Summary......Page 600
  Performance Profiling......Page 601
  Troubleshooting Performance Issues......Page 602
   Data Preparation......Page 603
   SSIS Package Execution Duration......Page 604
   Time Spent at Each Phase of the Data Flow Component......Page 605
   Elapsed Time for Data Flow Component Phases (Active Time vs. Total Time)......Page 606
  Monitoring SSIS Package Execution Performance......Page 608
  Per-Execution Performance Counters......Page 610
  Interactive Analysis of Performance Data......Page 611
  Summary......Page 620
   Row Count Values......Page 621
   Data Viewers......Page 622
   Data in Error Output......Page 624
   Execution Data Statistics......Page 625
   Data Tap......Page 628
   Error Dumps......Page 632
  Summary......Page 635
Index......Page 637
About the Authors......Page 669