You are previewing SQL Server 2012 Integration Services Design Patterns.
O'Reilly logo
SQL Server 2012 Integration Services Design Patterns

Book Description

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

SQL Server 2012 Integration Services Design Patterns does not focus on the problems to be solved; instead, the book delves 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, SQL Server 2012 Integration Services Design Patterns 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, and Dependency Services.

  • Takes you through solutions to several common data integration challenges

  • Demonstrates new features in SQL Server 2012 Integration Services

  • Teaches SSIS using practical examples

What you'll learn

  • Load data from flat file formats

  • Explore patterns for executing SSIS packages

  • Discover a pattern for loading XML data

  • Migrate SSIS packages through your application lifecycle without editing connections

  • Take advantage of SSIS 2012 Dependency Services

  • Build an SSIS Framework to support your application needs

Who this book is for

SQL Server 2012 Integration Services Design Patterns is for the data integration developer who is ready to take their SQL Server Integration Services (SSIS) skills to a more efficient level. It's for the developer interested in locating a previously-tested solution quickly. SQL Server 2012 Integration Services Design Patterns is a great book for ETL (extract, transform, and load) specialists and those seeking practical uses for new features in SQL Server 2012 Integration Services. It's an excellent choice for business intelligence and data warehouse developers.

Table of Contents

  1. Title
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. Foreword
  6. About the Authors
  7. About the Technical Reviewers
  8. Acknowledgments
  9. Chapter 1: Metadata Collection
    1. Introducing SQL Server Data Tools
    2. A Peek at the Final Product
    3. SQL Server Metadata
    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
    2. The SQL Server 2012 Integration Services Service
    3. Scheduling SSIS Package Execution
    4. Metadata-Driven Execution
    5. Execution from Managed Code
    6. Summary
  11. Chapter 3: Scripting Patterns
    1. The Toolset
    2. Should I Use Script?
    3. The Script Editor
    4. Script Maintenance Patterns
    5. Scripting Design Patterns
    6. Summary
  12. Chapter 4: SQL Server Source Patterns
    1. Setting up a Source
    2. Selecting a SQL Server Connection Manager and Provider
    3. Creating a SQL Server Source Component
    4. Writing a SQL Server Source Component Query
    5. Source Assistant
    6. Summary
  13. Chapter 5: Data Cleansing with Data Quality Services
    1. Overview of Data Quality Services
    2. Cleansing Data in the Data Flow
    3. Approving and Importing Cleansing Rules
    4. Summary
  14. Chapter 6: DB2 Source Patterns
    1. DB2 Database Family
    2. Selecting a DB2 Provider
    3. Connecting to a DB2 Database
    4. Querying the DB2 Database
    5. Summary
  15. Chapter 7: Flat File Source Patterns
    1. Flat File Sources
    2. Variable-Length-Rows
    3. Header and Footer Rows
    4. The Archive File Pattern
    5. Summary
  16. Chapter 8: Parallel Data Warehouse Patterns
    1. PDW Architecture Overview
    2. DWLoader vs. Integration Services
    3. ETL vs. ELT
    4. Installing the PDW Destination Adapter
    5. The Data Source
    6. The Data Flow
    7. Multi-Threading
    8. Summary
  17. Chapter 9: XML Patterns
    1. Using the XML Source
    2. Using a Script Component
    3. Summary
  18. Chapter 10: Expression Language Patterns
    1. Getting to Know the Expression Language
    2. Putting Expression Language to Work
    3. Summary
  19. Chapter 11: Data Warehouse Patterns
    1. Incremental Loads
    2. Incremental Loads in SSIS
    3. Data Errors
    4. Data Warehouse ETL Workflow
    5. Summary
  20. Chapter 12: Logging Patterns
    1. Essentials of Logging
    2. Logging in SSIS
    3. Summary
  21. Chapter 13: Slowly Changing Dimensions
    1. Slowly Changing Dimension Transform
    2. Third Party SCD Components
    3. MERGE Pattern
    4. Summary
  22. Chapter 14: Loading the Cloud
    1. Interacting with the Cloud
    2. Incremental Loads to SQL Azure
    3. Building the Cloud Loader
    4. Summary
  23. Chapter 15: Logging and Reporting Patterns
    1. Package Logging and Reporting
    2. Catalog Logging and Reporting
    3. Summary
  24. Chapter 16: Parent-Child Patterns
    1. Master Package Pattern
    2. Dynamic Child Package Pattern
    3. Child to Parent Variable Pattern
    4. Summary
  25. Chapter 17: 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
    4. Testing the Biml
    5. Using Biml as an SSIS Design Patterns Engine
    6. Time for a Test
    7. Summary
  26. Chapter 18: Configuration
    1. Parameters
    2. Connection Managers
    3. Parameter Configuration on the Server
    4. Parameters with DTEXEC
    5. Dynamic Configurations
    6. Summary
  27. Chapter 19: Deployment
    1. Project Deployment Model
    2. SSIS Catalog
    3. Deployment Methods
    4. Package Deployment Model
    5. Summary
  28. Chapter 20: Estimating ETL Projects
    1. What is being measured?
    2. Why estimate?
    3. Challenges
    4. The secret to estimating ETL project timelines is
    5. Summary
  29. Appendix A: Evolution of an SSIS Framework
    1. Starting in the Middle
    2. Monitoring Execution
    3. Reporting Execution Metrics
    4. Summary
  30. Index