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