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

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