Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Cover
Title
Copyright
Dedication
Contents at a Glance
Contents
Foreword
About the Authors
About the Technical Reviewers
Acknowledgments
Chapter 1: Metadata Collection
Introducing SQL Server Data Tools
A Peek at the Final Product
SQL Server Metadata
Setting Up the Central Repository
The Iterative Framework
Metadata Collection
Summary
Chapter 2: Execution Patterns
Building the Demonstration SSIS Package
Debug Execution
Command-Line Execution
Execute Package Utility
The SQL Server 2012 Integration Services Service
Integration Server Catalogs
Integration Server Catalog Stored Procedures
Scheduling SSIS Package Execution
Scheduling an SSIS Package
Scheduling a File System Package
Running SQLAgent Jobs with the Custom Execution Framework
Running the Custom Execution Framework with SQL Agent
Metadata-Driven Execution
Execution from Managed Code
The Demo Application
The frmMain Form
Summary
Chapter 3: Scripting Patterns
The Toolset
Should I Use Script?
The Script Editor
Project Explorer
Full .NET Runtime
Compiler
The Script Task
The Script Component
Script Maintenance Patterns
Code Reuse
Copy/Paste
External Assemblies
Custom Tasks/Components
Source Control
Scripting Design Patterns
Connection Managers and Scripting
Using Connection Managers in the Script Task
Using Connection Managers in the Script Component
Variables
Variable Visibility
Variable Syntax in Code
Variable Data Types
Naming Patterns
Summary
Chapter 4: SQL Server Source Patterns
Setting up a Source
Selecting a SQL Server Connection Manager and Provider
ADO.NET
ODBC
OLE DB
Creating a SQL Server Source Component
Writing a SQL Server Source Component Query
ADO.NET Data Access
OLE DB Data Access
Waste Not, Want Not
Data Translations
Source Assistant
Summary
Chapter 5: Data Cleansing with Data Quality Services
Overview of Data Quality Services
Using the Data Quality Client
Using DQS with SSIS
Cleansing Data in the Data Flow
Handling the Output of the DQS Cleansing Transform
Performance Considerations
Approving and Importing Cleansing Rules
Summary
Chapter 6: DB2 Source Patterns
DB2 Database Family
Selecting a DB2 Provider
Find Database Version
Pick Provider Vendor
Connecting to a DB2 Database
Querying the DB2 Database
DB2 Source Component Parameters
DB2 Source Component Dynamic Queries
Summary
Chapter 7: Flat File Source Patterns
Flat File Sources
Moving To SSIS!
Strong-Typing the Data
Introducing a Data-Staging Pattern
Variable-Length-Rows
Reading into a Data Flow
Splitting Record Types
Terminating the Streams
Header and Footer Rows
Consuming a Footer Row
Consuming a Header Row
Producing a Footer Row
Producing a Header Row
The Archive File Pattern
Summary
Chapter 8: Parallel Data Warehouse Patterns
PDW Architecture Overview
DWLoader vs. Integration Services
ETL vs. ELT
Installing the PDW Destination Adapter
The Data Source
The Data Flow
The Data Source
The Data Transformation
The Data Destination
Multi-Threading
Summary
Chapter 9: XML Patterns
Using the XML Source
Dealing with Multiple Outputs
Making Things Easier with XSLT
Using a Script Component
Configuring the Script Component
Processing XML with XmlSerializer
Processing XML with XmlReader and LINQ to XML
Summary
Chapter 10: Expression Language Patterns
Getting to Know the Expression Language
What is Expression Language?
Why Use Expressions?
Language Essentials
Limitations
Putting Expression Language to Work
Package Expressions
Variable Expressions
Connection Managers
Project-level Connection Managers
Control Flow
Data Flow Expressions
Summary
Chapter 11: Data Warehouse Patterns
Incremental Loads
What Is an Incremental Load?
Why Incremental Loads?
The Slowly Changing Dimension
Incremental Loads of Fact Data
Incremental Loads in SSIS
Native SSIS Components
The Slowly Changing Dimension Wizard
The MERGE Statement
Change Data Capture (CDC)
Data Errors
Simple Errors
Missing Data
Coding to Allow Errors
Data Warehouse ETL Workflow
Dividing Up the Work
One Package = One Unit of Work
Summary
Chapter 12: Logging Patterns
Essentials of Logging
Why Logging?
Elements of Logging
Logging in SSIS
SSIS Catalog Logging
Package Logging
Custom Logging
What to Capture?
Logging Systems
Summary
Chapter 13: Slowly Changing Dimensions
Slowly Changing Dimension Transform
Running the Wizard
Using the Transformations
Optimizing Performance
Third Party SCD Components
MERGE Pattern
Handling Type 1 Changes
Handling Type 2 Changes
Summary
Chapter 14: Loading the Cloud
Interacting with the Cloud
Incremental Loads to SQL Azure
Change Detection
New Rows (Only)
Building the Cloud Loader
Summary
Chapter 15: Logging and Reporting Patterns
Package Logging and Reporting
Setting Up Package Logging
Reporting on Package Logging
Design Pattern: Package Executions
Catalog Logging and Reporting
Setting Up Catalog Logging
Catalog Tables
Changing Logging Levels After the Fact
Design Patterns
Summary
Chapter 16: Parent-Child Patterns
Master Package Pattern
Assign the Child Package
Configure Parameter Binding
Dynamic Child Package Pattern
Child to Parent Variable Pattern
Summary
Chapter 17: Business Intelligence Markup Language
A Brief History of Business Intelligence Markup Language
Building Your First Biml File
Building a Basic Incremental Load SSIS Package
Adding Metadata
Specifying a Data Flow Task
Adding Transformations
Testing the Biml
Using Biml as an SSIS Design Patterns Engine
Time for a Test
Summary
Chapter 18: Configuration
Parameters
Configuring Your Package Using Parameters
Using the Parametrize Dialog
Creating Visual Studio Configurations
Specifying Entry Point Packages
Connection Managers
Parameter Configuration on the Server
Default Configuration
Server Environments
Default Parameter Values Using T-SQL
Package Execution through the SSIS Catalog
Parameters with DTEXEC
Projects on the File System
Projects in the SSIS Catalog
Dynamic Configurations
Configuring from a Database Table
Setting Values using a Script Task
Dynamic Package Executions
Summary
Chapter 19: Deployment
Project Deployment Model
SSIS Catalog
Deployment Methods
Deployment from the Command Line
Deployment Using Custom Code
Deployment Using PowerShell
Deployment Using SQL
Package Deployment Model
Summary
Chapter 20: Estimating ETL Projects
What is being measured?
Why estimate?
Challenges
It’s difficult because it requires—communication
It’s difficult because it requires guesswork
It’s difficult because it relies on technology
The secret to estimating ETL project timelines is
Don’t forget the little things
Plan for the unexpected
Know the personalities involved
Learn to do it right by doing it wrong
When the timeline slips, communicate early and often
Summary
Appendix A: Evolution of an SSIS Framework
Starting in the Middle
Introducing SSIS Applications
A Note About Relationships
Retrieving SSIS Applications in T-SQL
Retrieving SSIS Applications in SSIS
Monitoring Execution
Building Application Instance Logging
Building Package Instance Logging
Building Error Logging
Reporting Execution Metrics
Summary
Index
← Prev
Back
Next →
← Prev
Back
Next →