SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns

Released September 2012
Publisher(s): Apress
ISBN: 9781430237716

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 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

    Product information

    • Title: SQL Server 2012 Integration Services Design Patterns
    • Author(s):
    • Release date: September 2012
    • Publisher(s): Apress
    • ISBN: 9781430237716