Enterprise Extraction, Transformation & Loading with SQL Server 2008 Integration Services

Section A: SSIS Overview and Core Features

Module 01: SSIS Features and Concepts Overview

The role of ETL in Business Intelligence, SQL Server 2008 tools, SSIS ETL features, ETL Architecture, Visual Studio Design Environment, Creating Solutions and Projects Working with SSIS Projects, Properties Windows and viewers, Data Sources, Data Source Views, Connections

Module 02: Applying Control Flow Containers, Tasks and Constraints

Management Tasks, Standard Tasks, Execute SQL Task and parameters, Script Task, Sequence Container, Looping Containers, Standard and Expression based Constraints

Module 03: Applying Data Flow Transformations and Adapters

Data Flow source and destination adapters, Data expressions and conversion transformations, Data association and correlation transformations (IE: Lookup, Merge), Filtering, Data Paths and Data Viewers

Section B: Data Warehousing ETL Processing in SSIS

Module 04.1-Extr: Data Extraction Strategies

Heterogeneous sources, Data quality, Full and incremental source extraction, Using Data Source Views effectively, Dynamic source queries, Flat file sources, Source lineage tracking

Module 04.2-Trnsf: Advanced Data Preparation and Transformations

Data profiling, data cleansing (Fuzzy transformations), Script Component Sources and transformations

Module 04.3-Dim: Dimension Table ETL

Slowly Changing Dimension types and theory, Using the SCD wizard, Drilling into the SCD transformations, Customizing the SCD, Alternate SCD approaches, Inferred Members, Handling Snowflake and Parent-Child Hierarchies

Module 04.4-Fact: Fact Table ETL

Fact types and theory, Aspects of the fact table processing, Dimension lookups with the Lookup transformation, Missing Dimension Members, Measures and Calculations, Handling fact inserts and updates, Changing data grain, Processing Analysis Services Measure Group Partitions

Module 04.5-SSAS: Analysis Services Processing

Analysis Services processing types, Analysis Services Processing Task and Execute DDL Task, Dynamically creating and processing partitions, ASCMD command line and AMO API usage

Section C: Advanced SSIS Functionality

Module 05.1-Conf: Applying Dynamic Configurations and Built-in logging

Planning your configuration environment, SSIS Configuration types, dynamic Property Expressions, SSIS Logging, Extending SSIS auditing with Event Handlers

Module 05.2-Tran: Understanding SSIS Transaction Management and Restartability

Built-in checkpoints, How and when to use checkpoints, Managing RDBMS transactions, Rollback

considerations, Integrating Checkpoints with Transactions

Module 06: Coordinating Team Development and Deployment

Modular package design, Team development environment, Deploying Packages, SSIS Deployment Wizard

Module 07: Package Administration, Security and Execution

Package Execution Options, DTExec command line execution, SSIS Management features, SQL Server storage and security roles, Package encryption

Module 08: Troubleshooting and Optimizing Packages

Identifying package bottlenecks, Understanding the SSIS data flow architecture, Data Flow transformation types, Execution Trees, Engine Threads, Monitoring Data Flow execution with pipeline logging, Debugging Task with Breakpoints, SSIS Performance Monitor counters and tracking

Section D: ETL Case Study and SSIS Frameworks

Module 09: ETL Case Study and Package Frameworks

Case study examples: package design and performance, Integration with SQL Server 2008, using Merge and Change Data Capture features, Partition Table management, Creating an SSIS Framework for Auditing Troubleshooting, and Reporting