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

Book Description

An in-depth look at the radical changes to the newest release of SISS

Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. In addition to technical updates and additions, the authors present you with a new set of SISS best practices, based on years of real-world experience that have transpired since the previous edition was published.

  • Details the newest features of the 2012 SISS product release, which is the most significant release since 2005

  • Addresses the keys to a successful ETL solution, such as using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements

  • Includes additional case studies and tutorial examples to illustrate advanced concepts and techniques

Professional Microsoft SQL Server 2012 Integration Services is a valuable resource that meets the demands and high expectations of experienced SSIS professionals.

Table of Contents

  1. Cover
  2. Contents
  3. Chapter 1: Welcome to SQL Server Integration Services
    1. SQL Server SSIS Historical Overview
    2. What’s New in SSIS
    3. Tools of the Trade
    4. SSIS Architecture
    5. Editions of SQL Server
    6. Summary
  4. Chapter 2: The SSIS Tools
    1. Import and Export Wizard
    2. SQL Server Data Tools
    3. Creating Your First Package
    4. The Solution Explorer Window
    5. The SSIS Package Designer
    6. Management Studio
    7. Summary
  5. Chapter 3: SSIS Tasks
    1. SSIS Task Objects
    2. Looping and Sequence Tasks
    3. Script Task (.NET)
    4. Analysis Services Tasks
    5. Data Flow Task
    6. Data Preparation Tasks
    7. RDBMS Server Tasks
    8. Workflow Tasks
    9. SMO Administration Tasks
    10. Summary
  6. Chapter 4: Containers
    1. Task Host Containers
    2. Sequence Containers
    3. Groups
    4. For Loop Container
    5. Foreach Loop Container
    6. Summary
  7. Chapter 5: The Data Flow
    1. Understanding the Data Flow
    2. Data Viewers
    3. Sources
    4. Destinations
    5. Common Transformations
    6. Other Transformations
    7. Data Flow Example
    8. Summary
  8. Chapter 6: Using Variables, Parameters, and Expressions
    1. Dynamic Package Objects
    2. Understanding Data Types
    3. Using Variables and Parameters
    4. Working with Expressions
    5. Summary
  9. Chapter 7: Joining Data
    1. The Lookup Transformation
    2. Using the Merge Join Transformation
    3. Contrasting SSIS and the Relational Join
    4. Lookup Features
    5. Building the Basic Package
    6. Using the Lookup Transformation
    7. Cache Connection Manager and Cache Transform
    8. Summary
  10. Chapter 8: Creating an End-to-End Package
    1. Basic Transformation Tutorial
    2. Typical Mainframe ETL with Data Scrubbing
    3. Summary
  11. Chapter 9: Scripting in SSIS
    1. Introducing SSIS Scripting
    2. Getting Started in SSIS Scripting
    3. Using the Script Task
    4. Using the Script Component
    5. Essential Coding, Debugging, and Troubleshooting Techniques
    6. Summary
  12. Chapter 10: Loading A Data Warehouse
    1. Data Profiling
    2. Data Extraction and Cleansing
    3. Dimension Table Loading
    4. Fact Table Loading
    5. SSAS Processing
    6. Using a Master ETL Package
    7. Summary
  13. Chapter 11: Advanced Data Cleansing in SSIS
    1. Advanced Derived Column Use
    2. Advanced Fuzzy Lookup and Fuzzy Grouping
    3. DQS Cleansing
    4. Summary
  14. Chapter 12: Using The Relational Engine
    1. Data Extraction
    2. SQL Server Change Data Capture
    3. Data Loading
    4. Summary
  15. Chapter 13: Accessing Heterogeneous Data
    1. Excel and Access
    2. Importing from Oracle
    3. Using XML and Web Services
    4. Flat Files
    5. ODBC
    6. Other Heterogeneous Sources
    7. Summary
  16. Chapter 14: Reliability and Scalability
    1. Restarting Packages
    2. Package Transactions
    3. Error Outputs
    4. Scaling Out
    5. Summary
  17. Chapter 15: Understanding and Tuning the Data Flow Engine
    1. The SSIS Engine
    2. SSIS Data Flow Design and Tuning
    3. Pipeline Performance Monitoring
    4. Summary
  18. Chapter 16: SSIS Software Development Life Cycle
    1. Introduction to Software Development Life Cycles
    2. Versioning and Source Code Control
    3. Summary
  19. Chapter 17: Error and Event Handling
    1. Using Precedence Constraints
    2. Event Handling
    3. Breakpoints
    4. Error Rows
    5. Logging
    6. Summary
  20. Chapter 18: Programming and Extending SSIS
    1. The Sample Components
    2. The Pipeline Component Methods
    3. Building the Components
    4. Using the Components
    5. Upgrading to SQL Server 2012
    6. Summary
  21. Chapter 19: Adding a User Interface to Your Component
    1. Three Key Steps for Designing the UI: An Overview
    2. Building the User Interface
    3. Extending the User Interface
    4. Other UI Considerations
    5. Summary
  22. Chapter 20: External Management and WMI Task Implementation
    1. External Management of SSIS with Managed Code
    2. Package Operations
    3. Application Object Maintenance Operations
    4. Package Log Providers
    5. Package Configurations
    6. Windows Management Instrumentation Tasks
    7. Summary
  23. Chapter 21: Using SSIS with External Applications
    1. InfoPath Documents
    2. ASP.NET Applications
    3. Winform .NET Applications
    4. Summary
  24. Chapter 22: Administering SSIS
    1. Using the SSIS Catalog
    2. Deployment Models
    3. Using T-SQL with SSIS
    4. Creating a Central SSIS Server
    5. Clustering SSIS
    6. Package Configuration
    7. Command-Line Utilities
    8. Security
    9. Scheduling Packages
    10. 64-Bit Issues
    11. Monitoring Package Executions
    12. Performance Counters
    13. Summary
  25. Chapter 23: Case Study: A Programmatic Example
    1. What You Will Take Away
    2. Background
    3. Business Problem
    4. Solution Summary
    5. Solution Architecture
    6. Data Architecture
    7. Case Study Load Packages
    8. Case Study Invoice Matching Process
    9. Creating a Parent Driver Package
    10. Summary
  26. Introduction
  27. Advertisements