دانلود کتاب Microsoft SQL Server 2012 Integration Services

فهرست مطالب :

Foreword
Introduction
Part I: Overview
Chapter 1: SSIS Overview
Common Usage Scenarios for SSIS
Consolidation of Data from Heterogeneous Data Sources
Movement of Data Between Systems
Loading a Data Warehouse
Cleaning, Formatting, or Standardization of Data
Identification, Capture, and Processing of Data Changes
Coordination of Data Maintenance, Processing, or Analysis
Evolution of SSIS
Setting Up SSIS
SQL Server Features Needed for Data Integration
SQL Server Editions and Integration Services Features
Summary
Chapter 2: Understanding SSIS Concepts
Control Flow
Tasks
Precedence Constraints
Variables and Expressions
Containers
Connection Managers
Packages and Projects
Parameters
Log Providers
Event Handlers
Data Flow
Source Adapters
Destination Adapters
Transforms
SSIS Catalog
Overview
Catalog
Folders
Environments
References
Summary
Chapter 3: Upgrading to SSIS 2012
What’s New in SSIS 2012
Upgrade Considerations and Planning
Feature Changes in SSIS
Dependencies and Tools
Upgrade Requirements
Upgrade Scenarios
Unsupported Upgrade Scenarios
Upgrade Validation
Integration Services Upgrade
Upgrade Advisor
Performing Upgrade
Addressing Upgrade Issues and Manual Upgrade Steps
Conversion to Projects after Upgrade
Summary
Part II: Development
Chapter 4: New SSIS Designer Features
The Integration Services Designer
Visual Studio
Undo and Redo
Getting Started Window
Toolbox
Variables Window
Zoom Control
Autosave and Recovery
Status Icons
Annotations
Configuration and Deployment
Solution Explorer Changes
Parameter Tab
Visual Studio Configurations
Project Compilation
Deployment Wizard
Project Conversion Wizard
Import Project Wizard
New Tasks and Data Flow Components
Change Data Capture
Expression Task
DQS Cleansing Transform
ODBC Source and Destination
Control Flow
Expression Adorners
Connection Managers
Execute SQL Task
Data Flow
Connection Assistants
Improved Column Mapping
Editing Components in an Error State
Grouping
Simplified Data Viewers
Row Count and Pivot Transform User Interfaces
Flat File Source Changes
Scripting
Visual Studio Tools for Applications
Script Component Debugging
.NET 4 Framework Support
Expressions
Removal of the Character Limit
New Expression Functions
Summary
Chapter 5: Team Development
Improvements in SQL Server 2012
Package Format Changes
Visual Studio Configurations
Using Source Control Management with SSIS
Connecting to Team Foundation Server
Adding an SSIS Project to Team Foundation Server
Change Management
Changes to the SSIS Visual Studio Project File
Best Practices
Using Small, Simple Packages
One Developer Per Package
Consistent Naming Conventions
Summary
Chapter 6: Developing an SSIS Solution
SSIS Project Deployment Models
Package Deployment Model
Project Deployment Model
Develop an Integration Services Project
Creating an SSIS Project
Designing an Integration Services Data Flow
Using Parameters and the ForEach Container
Using the Execute Package Task
Building and Deploying an Integration Services Project
Summary
Chapter 7: Understanding SSIS Connectivity
Previous Connectivity Options in SSIS
Providers for Connectivity Technology
OLE DB, ADO.NET, and ODBC
New Connectivity Options in SSIS 2012
Introducing ODBC
ODBC Components for SSIS
ODBC Source
ODBC Destination
Connectivity Considerations for SSIS
64-Bit and SSIS
SSIS Tools on 64-Bit Architecture
Connectivity to Other Sources and Destinations
Connecting to Microsoft Excel and Access
Connecting to Oracle
Creating Custom Components
Using Script Components
Summary
Chapter 8: Working with Change Data Capture in SSIS 2012
CDC in SQL Server
Using CDC in SQL Server
CDC Scenarios in ETLs
Stages in CDC
CDC in SSIS 2012
CDC State
CDC Control Task
Data Flow Component: CDC Source
CDC Splitter Component
CDC for Oracle
Introduction
Components for Creating CDC for Oracle
CDC Service Configuration MMC
Oracle CDC Designer MMC
MSXDBCDC Database
Oracle CDC Service Executable (xdbcdcsvc.exe)
Data Type Handling
SSIS CDC Components
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
How Configurations Are Applied
What to Configure
Changes in SSIS 2012
Configuration in SSIS 2012
Parameters
Creating Package Parameters
Creating Project Parameters
API for Creating Parameters
Using Parameters
Configuring Parameters on the SSIS Catalog
Configuring, Validating, and Executing Packages and Projects
Configuration Through SSMS
Configuration Using SQL Agent, DTExec, and T-SQL
SSIS Environments
Evaluation Order of Parameters
Package Deployment Model and Backward Compatibility
Package Deployment Model
Best Practices for Configuring SSIS
Best Practices with Package Deployment Model
Best Practices with Project Deployment Model
Summary
Chapter 11: Running SSIS Packages
Ways to Run SSIS Packages
Package Locations
Configuring Packages
Error Dumps
Logging Options
Running Packages in the SSIS Catalog
Prepare Executions
Starting SSIS Package Executions
View Executions
Executions with T-SQL
Running Packages from SQL Agent
Create an SSIS Job Step
Execute Packages from the SSIS Catalog
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 Catalog Properties
Querying the SSIS Catalog Properties
Setting SSIS Catalog Properties
SSIS Projects and Packages
Deploy an SSIS Project to the SSIS Catalog
Learning About the SSIS Projects Deployed to the SSIS Catalog
Configuring SSIS Projects
Managing SSIS Projects in the SSIS Catalog
Running SSIS Packages in the SSIS Catalog
SSIS Environments
Creating SSIS Environments
Creating SSIS Environment Variables
Configuring SSIS Projects Using SSIS Environments
Configuring SSIS Projects Using Reference Values
Package Execution Using SSIS Environments
Managing SSIS Environment and Environment Variables
Summary
Chapter 13: SSIS PowerShell Magic
PowerShell Refresher
PowerShell and SQL Server
Managing SSIS with PowerShell
SSIS Management Object Model
PowerShell with SSIS Management Object Model
PowerShell and SSIS Using T-SQL
Advantages of Using PowerShell with SSIS
Summary
Chapter 14: SSIS Reports
Getting Started with SSIS Reports
Data Preparation
Monitoring SSIS Package Execution
Integration Services Dashboard
All Executions Report
All Validations and All Operations Reports
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
Overview
Load
Apply Parameters
Validate
Execute
The Data Flow Engine
Overview
Execution Control
Backpressure
Engine Tuning
Summary
Chapter 16: SSIS Catalog Deep Dive
SSIS Catalog Deep Dive
Creating the SSIS Catalog
Unit of Deployment to the SSIS Catalog
What Is Inside SSISDB?
SQL Server Instance Starts Up
SSIS Catalog and Logging Levels
Understanding the SSIS Package Execution Life Cycle
Stopping SSIS Package Executions
Using the Windows Application Event Log
SSIS Catalog Maintenance and SQL Server Agent Jobs
Backup and Restore of the SSIS Catalog
Back Up SSISDB
Restore SSISDB
Summary
Chapter 17: SSIS Security
Protect Your Package
Control Package Access
Package Encryption
Sensitive Variables and Parameters
Package Signing
Security in the SSIS Catalog
Security Overview
Manage Permissions
DDL Trigger
Running SSIS with SQL Agent
Requirements
Create Credentials
Create Proxy Accounts
Create SQL Agent Jobs
Summary
Chapter 18: Understanding SSIS Logging
Configure Logging Options
Choose Containers
Select Events
Add Log Providers
Log Providers
Text Files
SQL Server
SQL Server Profiler
Windows Event Log
XML Files
Logging in the SSIS Catalog
Logging Levels
Event Logs
Event Context Information
Advanced Logging Topics
Customizing Logging Fields
Logging with dtexec Utility
Developing Custom Log Providers
Summary
Chapter 19: Automating SSIS
Introduction to SSIS Automation
Programmatic Generation of SSIS Packages
Metadata-Driven Package Execution
Dynamic Package Generation
Handling Design-Time Events
Samples
Metadata-Based Execution
Custom Package Runner
Using PowerShell with the SSIS Management Object Model
Using PowerShell with SQL Agent
Alternative Solutions and Samples
Samples on Codeplex
Third-Party Solutions
Summary
Part V: Troubleshooting
Chapter 20: Troubleshooting SSIS Package Failures
Getting Started with Troubleshooting
Data Preparation
Troubleshooting Failures of SSIS Package Executions
Three Key Steps Toward Troubleshooting Failures of SSIS Package Executions
Execution Path
Finding the Root Cause of Failure
Troubleshooting the Execute Package Task and Child Package Executions
DiagnosticEx Events
Execute Package Task and Execution Path
Troubleshooting SSIS Package Execution Failures Scheduled with SQL Agent
Using CallerInfo to Determine SSIS Package Executions That Are Executed by SQL Agent
Using SQL Agent History Tables to Determine the SSIS Job Steps That Failed
Summary
Chapter 21: SSIS Performance Best Practices
Creating a Performance Strategy
OVAL Technique
Measuring SSIS Performance
Measuring System Performance
Measuring Performance of Data Flow Tasks
Designing for Performance
Parallelize Your Design
Using SQL Server Optimization Techniques
Bulk Loading Your Data
Keeping SSIS Operations in Memory
Optimizing SSIS Lookup Caching
Optimizing SSIS Infrastructure
Summary
Chapter 22: Troubleshooting SSIS Performance Issues
Performance Profiling
Troubleshooting Performance Issues
Data Preparation
Understanding SSIS Package Execution Performance
SSIS Package Execution Duration
Time Spent at Each Task in the SSIS Package
Time Spent at Each Phase of the Data Flow Component
Elapsed Time for Data Flow Component Phases (Active Time vs. Total Time)
Monitoring SSIS Package Execution Performance
Per-Execution Performance Counters
Interactive Analysis of Performance Data
Summary
Chapter 23: Troubleshooting Data Issues
Troubleshooting in the Design Environment
Row Count Values
Data Viewers
Data in Error Output
Breakpoints and Debug Windows
Troubleshooting in the Execution Environment
Execution Data Statistics
Data Tap
Error Dumps
Summary
Index
About the Authors