FastTrack SQL Server 2012 / 2014 Integration Services (SSIS)

#text-block-10 { margin-bottom:0px; text-align:left; }

SSIS Overview and Core Features

• How SSIS fits into the corporate data network and infrastructure
• Introduction to data integration, BI, and dimensional modeling
• SSIS features overview
• SQL Server 2012 tools

SSIS Development Environment Introduction

SSIS Control Flow Objects and Features

• Control flow and data flow principles
• Control flow features
• Containers
• SSIS expression language
• Tasks and constraints

Workflow Management in the Control Flow

Applying Data Flow Transformations and Adapters

• Data flow introduction and design environment
• Connecting the data flow to sources and destinations
• Transformations and paths

Working with Data in the Data Flow

Applying SSIS to Common Operations

Working With and Importing Files

• File-handling requirements and management
• Working with binary data
• Excel and 64-bit considerations
• Third-party custom components

Importing and Processing FTP Sourced Files

Data Source Extraction and Destination Optimization

• Extraction optimization
• Incremental extraction and Change Data Capture
• Data loading optimization

Incremental Extraction and ODBC Sources

Data Quality and Cleansing

• Data profiling and cleansing basics
• Fuzzy operations
• Fuzzy Lookup
• Fuzzy Grouping
• Text mining
• Script component
• Data Quality Services

Comparing the Fuzzy Lookup and DQS Cleansing Transformations

Applying SSIS in BI and Data Warehouse Solutions

Dimension ETL with SSIS

• Fact table ETL theory
• Fact loading concepts, columns, and mappings
• Workflow, precedence, and staging
• Identifying dimension surrogate keys
• Data preparation for fact tables
• Advanced concepts

#text-block-11 { margin-bottom:0px; text-align:left; }

Dimension Table ETL

Fact ETL with SSIS

• 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

Fact Table ETL

Processing Tabular and Multidimensional SSAS Objects in SSIS

• SSAS tabular and multidimensional
• Processing methods in SSIS
• Dynamic processing and partition creation

Analysis Services Processing

Project Deployment Model

Project Deployment Model: Configuration, Deployment & Security

• Project Deployment Model
• SSIS catalog features
• Project and package connections
• Project and package parameters
• Project Deployment Wizard
• SSIS environments
• Security and encryption

SSIS Catalog Configurations and Project Deployment

Project Deployment Model: Execution and Reporting

• Project deployment model utilities
• Package execution with T-SQL and DTExec
• Server reporting and logging

Executing Packages in the SSIS Catalog

Package Deployment Model

Package Deployment Model: Configuration and Deployment

• Package deployment model
• Package configurations
• Deploying packages

Working with Package Configurations

Package Deployment Model: Execution, Security & Logging

• Package deployment model utilities
• Package execution
• Package security and encryption
• Package logging

Package Logging and Execution

SSIS Solution and Performance Considerations

Transactions and Restartability

• SSIS transactions
• SQL Server database snapshots
• Restartability with checkpoints
• Debugging with breakpoints

Working with Transactions and Checkpoints

Optimization and Scalability

• SQL code versus SSIS pipeline
• Data flow engine internals
• General optimization techniques
• Performance root-cause analysis

Identifying and Resolving SSIS Performance Issues

Solution Approach and Team Development

• Modular and parent package design
• Team development considerations

Creating a Parent Package