Professional Microsoft SQL Server 2014 Integration Services

Professional Microsoft SQL Server 2014 Integration Services

by Brian Knight, Devin Knight, Jessica M. Moss, Mike Davis, Chris Rock
Released April 2014
Publisher(s): Wrox
ISBN: 9781118850879

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

Fill the gap between planning and doing with SSIS 2014

The 2014 release of Microsoft's SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, improved disaster recovery, increased scalability, and much more. The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change. New tools come with new best practices, and Professional Microsoft SQL Server 2014 Integration Services will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage.

  • Implement new best practices for effective use of SSIS

  • Work through tutorials for hands-on learning of complex techniques

  • Read case studies that illustrate the more advanced concepts

  • Learn directly from the foremost authority on SSIS

    • SQL Server Integration Services is a complex tool, but it's the lifeblood of your work. You need to know it inside out, and you must understand the full potential of its capabilities in order to use it effectively. You need to make sure the right architecture is in place. Professional Microsoft SQL Server 2014 Integration Services is your roadmap to understanding SSIS on a fundamental level, and setting yourself up for success.

    Table of contents

    1. 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
    2. Chapter 2: The SSIS Tools
      1. Import and Export Wizard
      2. SQL Server Data Tools
      3. The Solution Explorer Window
      4. The SSIS Package Designer
      5. Management Studio
      6. Summary
    3. 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
    4. Chapter 4: 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
    5. Chapter 5: Using Variables, Parameters, and Expressions
      1. Dynamic Package Objects
      2. Understanding Data Types
      3. Using Variables and Parameters
      4. Working with Expressions
      5. Summary
    6. Chapter 6: Containers
      1. Task Host Containers
      2. Sequence Containers
      3. Groups
      4. For Loop Container
      5. Foreach Loop Container
      6. Summary
    7. 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
    8. Chapter 8: Creating an End-to-End Package
      1. Basic Transformation Tutorial
      2. Typical Mainframe ETL with Data Scrubbing
      3. Summary
    9. 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
    10. Chapter 10: Advanced Data Cleansing in SSIS
      1. Advanced Derived Column Use
      2. Advanced Fuzzy Lookup and Fuzzy Grouping
      3. DQS Cleansing
      4. Master Data Management
      5. Summary
    11. Chapter 11: Incremental Loads in SSIS
      1. Control Table Pattern
      2. SQL Server Change Data Capture
      3. Summary
    12. Chapter 12: 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 13: Using the Relational Engine
      1. Data Extraction
      2. Data Loading
      3. Summary
    14. Chapter 14: 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
    15. Chapter 15: Reliability and Scalability
      1. Restarting Packages
      2. Package Transactions
      3. Error Outputs
      4. Scaling Out
      5. Summary
    16. Chapter 16: Understanding and Tuning the Data Flow Engine
      1. The SSIS Engine
      2. SSIS Data Flow Design and Tuning
      3. Pipeline Performance Monitoring
      4. Summary
    17. Chapter 17: SSIS Software Development Life Cycle
      1. Introduction to Software Development Life Cycles
      2. Versioning and Source Code Control
      3. Summary
    18. Chapter 18: Error and Event Handling
      1. Using Precedence Constraints
      2. Event Handling
      3. Breakpoints
      4. Error Rows
      5. Logging
      6. Summary
    19. Chapter 19: 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 2014
      6. Summary
    20. Chapter 20: 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
    21. Chapter 21: 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
    22. 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
    23. Appendix A: SSIS Crib Notes
    24. Appendix B: SSIS Internal Views and Stored Procedures
    25. Appendix C: Interviewing for an ETL Developer Position
    26. Introduction
    27. Advertisement
    28. End User License Agreement

    Product information

    • Title: Professional Microsoft SQL Server 2014 Integration Services
    • Author(s): Brian Knight, Devin Knight, Jessica M. Moss, Mike Davis, Chris Rock
    • Release date: April 2014
    • Publisher(s): Wrox
    • ISBN: 9781118850879