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

فهرست مطالب :

Professional Microsoft® SQL Server® 2014 Integration Services
Copyright
About the Authors
About The Technical Editor
Credits
Acknowledgments
Contents
Introduction
Who This Book Is For
What This Book Covers
How This Book Is Structured
What You Need to Use This Book
Conventions
Source Code
Errata
P2P.Wrox.Com
Chapter 1: Welcome to SQL Server Integration Services
SQL Server SSIS Historical Overview
What’s New in SSIS
Tools of the Trade
Import and Export Wizard
The SQL Server Data Tools Experience
SSIS Architecture
Packages
Control Flow
Data Flow
Variables
Parameters
Error Handling and Logging
Editions of SQL Server
Summary
Chapter 2: The SSIS Tools
Import and Export Wizard
SQL Server Data Tools
The Solution Explorer Window
The SSIS Toolbox
The Properties Windows
The SSIS Package Designer
Control Flow
Connection Managers
Variables
Data Flow
Parameters
Event Handlers
Package Explorer
Executing a Package
Management Studio
Summary
Chapter 3: SSIS Tasks
SSIS Task Objects
Using the Task Editor
The Task Editor Expressions Tab
Looping and Sequence Tasks
Script Task (.NET)
Analysis Services Tasks
Analysis Services Execute DDL Task
Analysis Services Processing Task
Data Mining Query Task
Data Flow Task
Data Preparation Tasks
Data Profiler
File System Task
Archiving a File
FTP Task
Getting a File Using FTP
Web Service Task
Retrieving Data Using the Web Service Task and XML Source Component
XML Task
Validating an XML File
RDBMS Server Tasks
Bulk Insert Task
Using the Bulk Insert Task
Execute SQL Task
Workflow Tasks
Execute Package Task
Execute Process Task
Message Queue Task
Send Mail Task
WMI Data Reader Task
WMI Event Watcher Task
Polling a Directory for the Delivery of a File
SMO Administration Tasks
Transfer Database Task
Transfer Error Messages Task
Transfer Logins Task
Transfer Master Stored Procedures Task
Transfer Jobs Task
Transfer SQL Server Objects Task
Summary
Chapter 4: The Data Flow
Understanding the Data Flow
Data Viewers
Sources
OLE DB Source
Excel Source
Flat File Source
Raw File Source
XML Source
ADO .NET Source
Destinations
Excel Destination
Flat File Destination
OLE DB Destination
Raw File Destination
Recordset Destination
Data Mining Model Training
DataReader Destination
Dimension and Partition Processing
Common Transformations
Synchronous versus Asynchronous Transformations
Aggregate
Conditional Split
Data Conversion
Derived Column
Lookup
Row Count
Script Component
Slowly Changing Dimension
Sort
Union All
Other Transformations
Audit
Character Map
Copy Column
Data Mining Query
DQS Cleansing
Export Column
Fuzzy Lookup
Fuzzy Grouping
Import Column
Merge
Merge Join
Multicast
OLE DB Command
Percentage and Row Sampling
Pivot Transform
Unpivot
Term Extraction
Term Lookup
Data Flow Example
Summary
Chapter 5: Using Variables, Parameters, and Expressions
Dynamic Package Objects
Variable Overview
Parameter Overview
Expression Overview
Understanding Data Types
SSIS Data Types
Date and Time Type Support
How Wrong Data Types and Sizes Can Affect Performance
Unicode and Non-Unicode Conversion Issues
Casting in SSIS Expressions
Using Variables and Parameters
Defining Variables
Defining Parameters
Variable and Parameter Data Types
Working with Expressions
C#-Like? Close, but Not Completely
The Expression Builder
Syntax Basics
Using Expressions in SSIS Packages
Summary
Chapter 6: Containers
Task Host Containers
Sequence Containers
Groups
For Loop Container
Foreach Loop Container
Foreach File Enumerator Example
Foreach ADO Enumerator Example
Summary
Chapter 7: Joining Data
The Lookup Transformation
Using the Merge Join Transformation
Contrasting SSIS and the Relational Join
Lookup Features
Building the Basic Package
Using a Relational Join in the Source
Using the Merge Join Transformation
Using the Lookup Transformation
Full-Cache Mode
No-Cache Mode
Partial-Cache Mode
Multiple Outputs
Expressionable Properties
Cascaded Lookup Operations
Cache Connection Manager and Cache Transform
Summary
Chapter 8: Creating an End-to-End Package
Basic Transformation Tutorial
Creating Connections
Creating the Control Flow
Creating the Data Flow
Completing the Package
Saving the Package
Executing the Package
Typical Mainframe ETL with Data Scrubbing
Creating the Data Flow
Handling Dirty Data
Finalizing
Handling More Bad Data
Looping and the Dynamic Tasks
Looping
Making the Package Dynamic
Summary
Chapter 9: Scripting in SSIS
Introducing SSIS Scripting
Getting Started in SSIS Scripting
Selecting the Scripting Language
Using the VSTA Scripting IDE
Example: Hello World
Adding Code and Classes
Using Managed Assemblies
Example: Using Custom .NET Assemblies
Using the Script Task
Configuring the Script Task Editor
The Script Task Dts Object
Accessing Variables in the Script Task
Connecting to Data Sources in a Script Task
Raising an Event in a Script Task
Writing a Log Entry in a Script Task
Using the Script Component
Differences from a Script Task
Configuring the Script Component Editor
Accessing Variables in a Script Component
Connecting to Data Sources in a Script Component
Raising Events
Logging
Example: Data Validation
Synchronous versus Asynchronous
Essential Coding, Debugging, and Troubleshooting Techniques
Structured Exception Handling
Script Debugging and Troubleshooting
Summary
Chapter 10: Advanced Data Cleansing in SSIS
Advanced Derived Column Use
Text Parsing Example
Advanced Fuzzy Lookup and Fuzzy Grouping
Fuzzy Lookup
Fuzzy Grouping
DQS Cleansing
Data Quality Services
DQS Cleansing Transformation
Master Data Management
Master Data Services
Summary
Chapter 11: Incremental Loads in SSIS
Control Table Pattern
Querying the Control Table
Querying the Source Table
Updating the Control Table
SQL Server Change Data Capture
Benefits of SQL Server CDC
Preparing CDC
Capture Instance Tables
The CDC API
Using the SSIS CDC Tools
Summary
Chapter 12: Loading a Data Warehouse
Data Profiling
Initial Execution of the Data Profiling Task
Reviewing the Results of the Data Profiling Task
Turning Data Profile Results into Actionable ETL Steps
Data Extraction and Cleansing
Dimension Table Loading
Loading a Simple Dimension Table
Loading a Complex Dimension Table
Considerations and Alternatives to the SCD Transformation
Fact Table Loading
SSAS Processing
Using a Master ETL Package
Summary
Chapter 13: Using the Relational Engine
Data Extraction
SELECT * Is Bad
WHERE Is Your Friend
Transform during Extract
Many ANDs Make Light Work
SORT in the Database
Modularize
SQL Server Does Text Files Too
Using Set-Based Logic
Data Loading
Database Snapshots
The MERGE Operator
Summary
Chapter 14: Accessing Heterogeneous Data
Excel and Access
64-Bit Support
Working with Excel Files
Working with Access
Importing from Oracle
Oracle Client Setup
Importing Oracle Data
Using XML and Web Services
Configuring the Web Service Task
Working with XML Data as a Source
Flat Files
Loading Flat Files
Extracting Data from Flat Files
ODBC
Other Heterogeneous Sources
Summary
Chapter 15: Reliability and Scalability
Restarting Packages
Simple Control Flow
Containers within Containers and Checkpoints
Variations on a Theme
Inside the Checkpoint File
Package Transactions
Single Package, Single Transaction
Single Package, Multiple Transactions
Two Packages, One Transaction
Single Package Using a Native Transaction in SQL Server
Error Outputs
Scaling Out
Architectural Features
Scaling Out Memory Pressures
Scaling Out by Staging Data
Scaling Out with Parallel Loading
Summary
Chapter 16: Understanding and Tuningthe Data Flow Engine
The SSIS Engine
Understanding the SSIS Data Flow and Control Flow
Handling Workflows with the Control Flow
Data Processing in the Data Flow
Memory Buffer Architecture
Types of Transformations
Advanced Data Flow Execution Concepts
SSIS Data Flow Design and Tuning
Data Flow Design Practices
Optimizing Package Processing
Troubleshooting Data Flow Performance Bottlenecks
Pipeline Performance Monitoring
Summary
Chapter 17: SSIS Software Development Life Cycle
Introduction to Software Development Life Cycles
SDLCs: A Brief History
Types of Software Development Life Cycles
Versioning and Source Code Control
Subversion (SVN )
Team Foundation Server, Team System, and SSIS
Summary
Chapter 18: Error and Event Handling
Using Precedence Constraints
Precedence Constraint Basics
Advanced Precedence Constraints and Expressions
Event Handling
Events
Using Event Handlers
Event Handler Inheritance
Breakpoints
Error Rows
Logging
Logging Providers
Log Events
Catalog Logging
Summary
Chapter 19: Programming and Extending SSIS
The Sample Components
Component 1: Source Adapter
Component 2: Transform
Component 3: Destination Adapter
The Pipeline Component Methods
Design-Time Functionality
Runtime
Connection Time
Building the Components
Preparation
Building the Source Component
Building the Transformation Component
Building the Destination Adapter
Using the Components
Installing the Components
Debugging Components
Design Time
Building the Complete Package
Runtime Debugging
Upgrading to SQL Server 2014
Summary
Chapter 20: Adding a User Interface toYour Component
Three Key Steps for Designing the UI: An Overview
Building the User Interface
Adding the Project
Implementing IDtsComponentUI
Setting the UITypeName
Building the Form
Extending the User Interface
Runtime Connections
Component Properties
Handling Errors and Warnings
Column Properties
Other UI Considerations
Summary
Chapter 21: External Management and WMI TaskImplementation
External Management of SSIS with Managed Code
Setting Up a Test SSIS Package for Demonstration Purposes
The Managed Object Model Code Library
Catalog Management
Folder Management
Environments
The DTS Runtime Managed Code Library
SSIS Deployment Projects
Parameter Objects
Server Deployment
Executing SSIS Packages Deployed to the SSIS Catalog
Environment References
Package Operations
Application Object Maintenance Operations
Package Operations
Package Monitoring
Project, Folder, and Package Listing
A Package Management Example
Package Log Providers
Specifying Events to Log
Programming to Log Providers
SQL Server 2014 Operation Logs
Package Configurations
Creating a Configuration
Programming the Configuration Object
Configuration Object
Windows Management Instrumentation Tasks
WMI Reader Task Explained
WMI Data Reader Example
WMI Event Watcher Task
WMI Event Watcher Task Example
Summary
Chapter 22: Administering SSIS
Using the SSIS Catalog
Setting the SSIS Catalog Properties
SSISDB
Deployment Models
Project Deployment Model
Package Deployment Model
Using T-SQL with SSIS
Executing Packages
Using Parameters
Querying Tables for Parameter Values
Using Environments
Using Data Taps
Creating a Central SSIS Server
Clustering SSIS
Package Configuration
Command-Line Utilities
DTExec
DTExecUI
DTUtil
Security
Securing the SSIS Catalog
Legacy Security
Scheduling Packages
SQL Server Agent
Proxy Accounts
64-Bit Issues
Monitoring Package Executions
Built-in Reporting
Custom Reporting
Performance Counters
Summary
Appendix A: SSIS Crib Notes
When to Use Control Flow Tasks
When to Use Data Flow Transforms
Common Expressions and Scripts
Appendix B: SSIS Internal Views and StoredProcedures
Views
Stored Procedures
Appendix C: Interviewing for an ETL DeveloperPosition
Questions
Answers
Index
Advertisement