Pro SQL Server 2012 Integration Services

Pro SQL Server 2012 Integration Services

by David Dye, Michael Coles, Francis Rodrigues
Released June 2012
Publisher(s): Apress
ISBN: 9781430236924

Explore a preview version of Pro SQL Server 2012 Integration Services right now.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Book Description

Pro SQL Server 2012 Integration Services teaches how to take advantage of the powerful extract, transform, and load (ETL) platform included with Microsoft SQL Server 2012. You'll learn to build scalable, robust, performance-driven enterprise ETL solutions that save time and make money for your company. You'll learn to avoid common ETL development pitfalls and how to extend the power of your ETL solutions to include virtually any possible transformation on data from any conceivable source. SQL Server Integration Services (SSIS) facilitates an unprecedented ability to load data from anywhere, perform any type of manipulation on it, and store it to any source.

Whether you are populating databases, retrieving data from the Web, or performing complex calculations on large data sets, SSIS gives you the tools to get the job done. And this book gives you the knowledge to take advantage of everything SSIS offers.

  • Helps you design and develop robust, efficient, scalable ETL solutions

  • Walks you through using the built-in, stock components

  • Shows how to programmatically extend the power of SSIS to cover any possible scenario

    •

    Table of Contents

    1. Title
    2. Dedication
    3. Contents at a Glance
    4. Contents
    5. About the Authors
    6. About the Technical Reviewer
    7. CHAPTER 1: Introducing Integration Services
      1. A Brief History of Microsoft ETL
      2. What Can SSIS Do for You?
      3. What Is Enterprise ETL?
      4. SSIS Architecture
      5. New SSIS Features
      6. Our Favorite People and Places
      7. Summary
    8. CHAPTER 2: BIDS and SSMS
      1. SQL Server Business Intelligence Development Studio
      2. Integration Services
      3. SQL Server Management Studio
      4. Summary
    9. CHAPTER 3: Hello World—Your First SSIS 2012 Package
      1. Integration Services Project
      2. Hello World
      3. Real World
      4. Summary
    10. CHAPTER 4: Connection Managers
      1. Commonly Used Connection Managers
      2. Other Connection Managers
      3. Summary
    11. CHAPTER 5: Control Flow Basics
      1. What Is a Control Flow?
      2. SSIS Toolbox for Control Flow
      3. Favorite Tasks
      4. Common Tasks
      5. Precedence Constraints
      6. Basic Containers
      7. Breakpoints
      8. Summary
    12. CHAPTER 6: Advanced Control Flow Tasks
      1. Advanced Tasks
      2. Advanced Containers
      3. Summary
    13. CHAPTER 7: Source and Destination Adapters
      1. The Data Flow
      2. Sources and Destinations
      3. Source Assistant
      4. Destination Assistant
      5. Database Sources and Destinations
      6. Files
      7. Special-Purpose Adapters
      8. Analysis Services
      9. Summary
    14. CHAPTER 8: Data Flow Transformations
      1. High-Level Data Flow
      2. Types of Transformations
      3. Row Transformations
      4. Rowset Transformations
      5. Splits and Joins
      6. Auditing
      7. Business Intelligence Transformations
      8. Summary
    15. CHAPTER 9: Variables, Parameters, and Expressions
      1. What Are Variables and Expressions?
      2. What Are Parameters?
      3. SSIS Data Types
      4. Variable Scope, Default Values, and Namespaces
      5. System Variables
      6. Accessing Variables
      7. Dynamic SQL
      8. Passing Variables
      9. SSIS Expression Language
      10. Summary
    16. CHAPTER 10: Scripting
      1. Script Task
      2. Advanced Functionality
      3. Script Component Source
      4. Synchronous Script Component Transformation
      5. Asynchronous Script Component Transformation
      6. Script Component Destination
      7. Summary
    17. CHAPTER 11: Events and Error Handling
      1. SSIS Events
      2. Logging Events
      3. Script Events
      4. Event Handlers
      5. Summary
    18. CHAPTER 12: Data Profiling and Scrubbing
      1. Data Profiling
      2. Fuzzy Searching
      3. Data Previews
      4. Summary
    19. CHAPTER 13: Logging and Auditing
      1. Logging
      2. Summary Auditing
      3. Simple Data Lineage
      4. Summary
    20. CHAPTER 14: Heterogeneous Sources and Destinations
      1. SQL Server Sources and Destinations
      2. Other RDBMS Sources and Destinations
      3. Flat File Sources and Destinations
      4. Excel Sources and Destinations
      5. XML Sources
      6. Raw File Sources and Destinations
      7. SQL Server Analysis Services Sources
      8. Recordset Destination
      9. Summary
    21. CHAPTER 15: Data Flow Tuning and Optimization
      1. Limiting Rows at the Database
      2. Performing Joins in the Database
      3. Sorting in the Database
      4. Performing Complex Preprocessing at the Database
      5. Ensuring Security and “Read Auditing”
      6. Pulling Too Many Columns
      7. Using Execution Trees
      8. Implementing Parallelism
      9. Summary
    22. CHAPTER 16: Parent-Child Design Pattern
      1. Understanding the Parent-Child Design Pattern
      2. Using Parameters to Pass Values
      3. Working with Shared Configuration Information
      4. Overriding Properties
      5. Logging
      6. Implementing Data-Driven ETL
      7. Summary
    23. CHAPTER 17: Dimensional Data ETL
      1. Introducing Dimensional Data
      2. Creating Quick Wins
      3. Understanding Slowly Changing Dimensions
      4. Summary
    24. CHAPTER 18: Building Robust Solutions
      1. What Makes a Solution Robust
      2. Resilience
      3. Dynamism
      4. Accountability
      5. Summary
    25. CHAPTER 19: Deployment Model
      1. The Build Process
      2. The Deployment Process
      3. Environments
      4. Execution
      5. The Import Process
      6. The Migration Process
      7. Summary
    26. Index

    Product Information

    • Title: Pro SQL Server 2012 Integration Services
    • Author(s): David Dye, Michael Coles, Francis Rodrigues
    • Release date: June 2012
    • Publisher(s): Apress
    • ISBN: 9781430236924