|
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
|
|
|