This book provides ETL developers and database administrators (DBAs) with comprehensive information on SQL Server 2012 Integration Services, and deep dive discussions on core features. The book includes special coverage of new features introduced with SQL Server 2012. Readers will gain an understanding not only of SSIS fundamentals, but get introduced to more advanced SSIS usage patterns, best practices, and see how to perform them effectively and take advantage of the newest SSIS features.
This is not a beginner book; readers should already have a basic knowledge of SSIS and be proficient with basic SSIS usage. The book has 23 chapters arranged in three major sections:
• Overview
• Development
• Database Admin
• Deep-Dive
• Troubleshooting
You can view the complete table of contents below.
Table of Contents
Part 1 - Overview
Chapter 1 SSIS Overview
Common Usage Scenarios for SSIS
Evolution of SSIS
Setting Up SSIS
Summary
Chapter 2 Understanding SSIS Concepts
Control Flow
Data Flow
SSIS Catalog
Summary
Chapter 3 Upgrading to SSIS 2012
What’s New in SSIS 2012
Upgrade Considerations and Planning
Integration Services Upgrade
Summary
Part 2 - Development
Chapter 4 New SSIS Designer Features
The Integration Services Designer
Configuration and Deployment
New Tasks and Data Flow Components
Control Flow
Data Flow
Scripting
Expressions
Summary
Chapter 5 Team Development
Improvements in SQL Server 2012
Using Source Control Management with SSIS
Best Practices
Summary
Chapter 6 Developing an SSIS Solution
SSIS Project Deployment Models
Develop an Integration Services Project
Summary
Chapter 7 Understanding SSIS Connectivity
Previous Connectivity Options in SSIS
New Connectivity Options in SSIS 2012
Connectivity Considerations for SSIS
Connectivity to Other Sources and Destinations
Summary
Chapter 8 Working with Change Data Capture in SSIS 2012
CDC in SQL Server
CDC in SSIS 2012
CDC for Oracle
Summary
Chapter 9 Data Cleansing Using SSIS
Data Profiling Task
Fuzzy Lookup Transformation
Fuzzy Grouping Transformation
Data Quality Services Cleansing Transform
Summary
Part III - Database Admin
Chapter 10 Configuration in SSIS
Configuration Basics
Configuration in SSIS 2012
Configuring Parameters on the SSIS Catalog
Package Deployment Model and Backward Compatibility
Best Practices for Configuring SSIS
Summary
Chapter 11 Running SSIS Packages
Ways to Run SSIS Packages
Running Packages in the SSIS Catalog
Running Packages from SQL Agent
Running Packages via PowerShell
Creating and Running SSIS Packages Programmatically
Summary
Chapter 12 SSIS T-SQL Magic
Overview of SSIS Stored Procedures and Views
Integration Services Catalog
SSIS Projects and Packages
SSIS Environments
Summary
Chapter 13 SSIS PowerShell Magic
PowerShell Refresher
PowerShell and SQL Server
Managing SSIS with PowerShell
Advantages of Using PowerShell with SSIS
Summary
Chapter 14 SSIS Reports
Getting Started with SSIS Reports
Data Preparation
Monitoring SSIS Package Execution
Using SSIS Reports to Troubleshoot SSIS Package Execution
Using the Execution Performance Report to Identify Performance Trends
Summary
Part IV - Deep-Dive
Chapter 15 SSIS Engine Deep Dive
The Control Flow Engine
The Data Flow Engine
Summary
Chapter 16 SSIS Catalog Deep Dive
SSIS Catalog Deep Dive
Understanding the SSIS Package Execution Life Cycle
SSIS Catalog Maintenance and SQL Server Agent Jobs
Backup and Restore of the SSIS Catalog
Summary
Chapter 17 SSIS Security
Protect Your Package
Security in the SSIS Catalog
Running SSIS with SQL Agent
Summary
Chapter 18 Understanding SSIS Logging
Configure Logging Options
Log Providers
Logging in the SSIS Catalog
Advanced Logging Topics
Summary
Chapter 19 Automating SSIS
Introduction to SSIS Automation
Dynamic Package Generation
Metadata-Based Execution
Alternative Solutions and Samples
Summary
Part V - Troubleshooting
Chapter 20 Troubleshooting SSIS Package Failures
Getting Started with Troubleshooting
Data Preparation
Troubleshooting Failures of SSIS Package Executions
Troubleshooting the Execute Package Task and Child Package Executions
Troubleshooting SSIS Package Execution Failures Scheduled with SQL Agent