SQL Server Integration Services Design Patterns, Second Edition

SQL Server Integration Services Design Patterns, Second Edition

Released December 2014
Publisher(s): Apress
ISBN: 9781484200827

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

SQL Server Integration Services Design Patterns is newly-revised for SQL Server 2014, and is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book help to solve common problems encountered when developing data integration solutions. The patterns and solution examples in the book increase your efficiency as an SSIS developer, because you do not have to design and code from scratch with each new problem you face. The book's team of expert authors take you through numerous design patterns that you'll soon be using every day, providing the thought process and technical details needed to support their solutions.

SQL Server Integration Services Design Patterns goes beyond the surface of the immediate problems to be solved, delving into why particular problems should be solved in certain ways. You'll learn more about SSIS as a result, and you'll learn by practical example. Where appropriate, the book provides examples of alternative patterns and discusses when and where they should be used. Highlights of the book include sections on ETL Instrumentation, SSIS Frameworks, Business Intelligence Markup Language, and Dependency Services.

  • Takes you through solutions to common data integration challenges
  • Provides examples involving Business Intelligence Markup Language
  • Teaches SSIS using practical examples

Table of contents

  1. Cover
  2. Title
  3. Copyright
  4. Contents at a Glance
  5. Contents
  6. First-Edition Foreword
  7. About the Authors
  8. About the Technical Reviewer
  9. Chapter 1: Metadata Collection
    1. About SQL Server Data Tools
    2. A Peek at the Final Product
    3. SQL Server Metadatacatalog
      1. sys.dm_os_performance_counters
      2. sys.dm_db_index_usage_stats
      3. sys.dm_os_sys_info
      4. sys.tables
      5. sys.indexes
      6. sys.partitions
      7. sys.allocation_units
    4. Setting Up the Central Repository
    5. The Iterative Framework
    6. Metadata Collection
    7. Summary
  10. Chapter 2: Execution Patterns
    1. Building the Demonstration SSIS Package
      1. Debug Execution
      2. Command-Line Execution
      3. Execute Package Utility
    2. The SQL Server 2014 Integration Services Service
      1. Integration Services Catalogs
      2. Integration Server Catalog Stored Procedures
    3. Scheduling SSIS Package Execution
      1. Scheduling an SSIS Package
      2. Scheduling a File System Package
      3. Running SQL Server Agent Jobs with the Custom Execution Framework
      4. Running the Custom Execution Framework with SQL Server Agent
      5. Execute Package Task
    4. Execution from Managed Code
      1. The Demo Application
      2. The frmMain Form
    5. Conclusion
  11. Chapter 3: Scripting Patterns
    1. The Toolset
      1. Should I Use Script?
      2. The Script Editor
      3. The Script Task
      4. The Script Component
    2. Script Maintenance Patterns
      1. Code Reuse
      2. Source Control
    3. Scripting Design Patterns
      1. Connection Managers and Scripting
      2. Variables
      3. Naming Patterns
    4. Conclusion
  12. Chapter 4: SQL Server Source Patterns
    1. Setting Up a Source
    2. Selecting a SQL Server Connection Manager and Provider
      1. ADO.NET
      2. ODBC
      3. OLE DB
    3. Creating a SQL Server Source Component
    4. Writing a SQL Server Source Component Query
      1. ADO.NET Data Access
      2. OLE DB Data Access
      3. Waste Not, Want Not
      4. Data Translations
    5. Source Assistant
    6. Summary
  13. Chapter 5: Data Correction with Data Quality Services
    1. Overview of Data Quality Services
      1. Using the Data Quality Client
    2. Using DQS with SSIS
      1. DQS Cleansing Transform
      2. DQS Extensions on CodePlex
    3. Cleansing Data in the Data Flow
      1. Handling the Output of the DQS Cleansing Transform
      2. Performance Considerations
    4. Approving and Importing Cleansing Rules
    5. Conclusion
  14. Chapter 6: DB2 Source Patterns
    1. DB2 Database Family
    2. Selecting a DB2 Provider
      1. Find the Database Version
      2. Pick Provider Vendor
    3. Connecting to a DB2 Database
    4. Querying the DB2 Database
      1. DB2 Source Component Parameters
      2. DB2 Source Component Dynamic Queries
    5. Summary
  15. Chapter 7: Flat File Source Patterns
    1. Flat File Sources
      1. Moving to SSIS!
      2. Strong-Typing the Data
      3. Introducing a Data-Staging Pattern
    2. Variable-Length Rows
      1. Reading into a Data Flow
      2. Splitting Record Types
      3. Terminating the Streams
    3. Header and Footer Rows
      1. Consuming a Footer Row
      2. Consuming a Header Row
      3. Producing a Footer Row
      4. Producing a Header Row
    4. The Archive File Pattern
    5. Summary
  16. Chapter 8: Loading a PDW Region in APS
    1. Massively Parallel Processing
    2. APS Appliance Overview
      1. Hardware Architecture
      2. Software Architecture
      3. Shared-Nothing Architecture
      4. Clustered Columnstore Indexes
    3. Loading Data
      1. DWLoader vs. Integration Services
      2. ETL vs. ELT
    4. Data Import Pattern for PDW
      1. Prerequisites
      2. Preparing the Data
      3. Package Overview
      4. The Data Source
      5. The Data Transformation
      6. The Data Destination
      7. Multithreading
    5. Limitations
    6. Summary
  17. Chapter 9: XML Patterns
    1. Using the XML Source
      1. Dealing with Multiple Outputs
      2. Making Things Easier with XSLT
    2. Using a Script Component
      1. Configuring the Script Component
      2. Processing XML with XmlSerializer
      3. Processing XML with XmlReader and LINQ to XML
    3. Conclusion
  18. Chapter 10: Expression Language Patterns
    1. Getting to Know the Expression Language
      1. What Is the Expression Language?
      2. Why Use Expressions?
      3. Language Essentials
      4. Limitations
    2. Putting the Expression Language to Work
      1. Package Expressions
      2. Variable Expressions
      3. Connection Managers
      4. Project-Level Connection Managers
      5. Control Flow
      6. Data Flow Expressions
    3. Conclusion
  19. Chapter 11: Data Warehouse Patterns
    1. Incremental Loads
      1. What Is an Incremental Load?
      2. Why Incremental Loads?
      3. The Slowly Changing Dimension
      4. Incremental Loads of Fact Data
    2. Incremental Loads in SSIS
      1. Native SSIS Components
      2. The Slowly Changing Dimension Wizard
      3. The MERGE Statement
      4. Change Data Capture (CDC)
    3. Data Errors
      1. Simple Errors
      2. Missing Data
      3. Coding to Allow Errors
    4. Data Warehouse ETL Workflow
      1. Dividing Up the Work
      2. One Package = One Unit of Work
    5. Conclusion
  20. Chapter 12: OData Source
    1. Understanding the OData Protocol
      1. Data Type Mappings
      2. Query Options
    2. Configuring the OData Connection Manager
    3. Enabling Microsoft Online Services Authentication
    4. Configuring the Source Component
    5. Overriding Data Types
    6. Conclusion
  21. Chapter 13: Slowly Changing Dimensions
    1. The Slowly Changing Dimension Transform
      1. Running the Wizard
      2. Using the Transformations
      3. Optimizing Performance
    2. Third-Party SCD Components
    3. Merge Pattern
      1. Handling Type 1 Changes
      2. Handling Type 2 Changes
    4. Conclusion
  22. Chapter 14: Loading the Cloud
    1. Interacting with the Cloud
    2. Incremental Loads to Azure SQL Database
      1. Change Detection
      2. New Rows (Only)
    3. Building the Cloud Loader
    4. Conclusion
  23. Chapter 15: Logging and Reporting Patterns
    1. Package Logging and Reporting
      1. Setting Up Package Logging
      2. Reporting on Package Logging
      3. Design Pattern: Package Executions
    2. Catalog Logging and Reporting
      1. Setting Up Catalog Logging
      2. Catalog Tables
      3. Changing Logging Levels After the Fact
    3. Design Patterns
      1. Changing the Logging Level
      2. Using the Existing Reports
      3. Creating New Reports
    4. Summary
  24. Chapter 16: Parent-Child Patterns
    1. Master Package Pattern
      1. Assign the Child Package
      2. Configure Parameter Binding
    2. Dynamic Child Package Pattern
    3. Child-to-Parent Variable Pattern
    4. Conclusion
  25. Chapter 17: Configuration
    1. Parameters
      1. Configuring Your Package Using Parameters
      2. Using the Parametrize Dialog
      3. Creating Visual Studio Configurations
      4. Specifying Entry-Point Packages
    2. Connection Managers
    3. Parameter Configuration on the Server
      1. Default Configuration
      2. Server Environments
      3. Default Parameter Values Using T-SQL
      4. Package Execution Through the SSIS Catalog
    4. Parameters with DTEXEC
      1. Projects on the File System
      2. Projects in the SSIS Catalog
    5. Dynamic Configurations
      1. Configuring from a Database Table
      2. Setting Values Using a Script Task
      3. Dynamic Package Executions
    6. Conclusion
  26. Chapter 18: Deployment
    1. Project Deployment Model
    2. SSIS Catalog
    3. Deployment Methods
      1. Deployment from the Command Line
      2. Deployment Using Custom Code
      3. Deployment Using PowerShell
      4. Deployment Using SQL
    4. Package Deployment Model
    5. Conclusion
  27. Chapter 19: Business Intelligence Markup Language
    1. A Brief History of Business Intelligence Markup Language
    2. Building Your First Biml File
    3. Building a Basic Incremental Load SSIS Package
      1. Creating Databases and Tables
      2. Adding Metadata
      3. Specifying a Data Flow Task
      4. Adding Transforms
      5. Testing the Biml
    4. Using Biml as an SSIS Design Patterns Engine
    5. Time for a Test
    6. Conclusion
  28. Chapter 20: Biml and SSIS Frameworks
    1. Using Biml with an SSIS Framework
      1. Adding SSIS Package Metadata to the Framework
      2. Executing the Biml File
    2. Generating the SSIS Command-Line
    3. Summarizing
  29. Appendix A: Evolution of an SSIS Framework
    1. Starting in the Middle
      1. Introducing SSIS Applications
      2. A Note About Relationships
      3. Retrieving SSIS Applications in T-SQL
      4. Retrieving SSIS Applications in SSIS
    2. Monitoring Execution
      1. Building Application Instance Logging
      2. Building Package Instance Logging
      3. Building Error Logging
    3. Reporting Execution Metrics
    4. Conclusion
  30. Index

Product information

  • Title: SQL Server Integration Services Design Patterns, Second Edition
  • Author(s):
  • Release date: December 2014
  • Publisher(s): Apress
  • ISBN: 9781484200827