| |
There are Two Keys to a Successful ETL Solution: Using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements. With SQL Server 2014 integration services, half the battle is won. The out-of-the-box features provide the depth and breadth of functionality and flexibility needed to create scalable solutions. Even more, the usability and rapid learning curve reduce solution development time.
The second ingredient is correct ETL architecture and design. When considerable effort is spent testing scenarios and proving out designs, valuable testing, implementation and deployment time is lost. This book fills that gap.
Professional SQL Server 2014 integration services, fully cover the new features of the 2014 product release, expected in early 2014. The book is about 20 percent revised, reflecting changes in how the product looks and works. In addition to technical updates and additions, the authors add a new set of best practices for effective use of SSIS, more case studies and tutorial examples to illustrate advanced concepts and techniques.
Table of Contents
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 Task
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
Record set Destination
Data Mining Model Training
Data Reader 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
For each Loop Container
For each File Enumerator Example
For each 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 ISBN - 9788126550395
|
| |
|