Introduction to SSIS & Datawarehouse

• Need for SSIS & ETL
• Datawarehouse Design and SSIS

DW Components and SSIS Tools

• SSIS Architecture and DW Concepts
• SSIS Configuration and Catalog DB
• Control Flow Tasks Architecture
• Data Flow Tasks Architecture
• Data Pipelines and Data Buffers

Basic SSIS Entities

• SSIS Data Types and Conversions
• SSIS Local & Global Variables
• Dynamic Precedence Constraints
• Dynamic Connection Managers
• Data Flow Transformations usage
• Handling Enumerations in SSIS
• LOOPs and Indexed Connections

SSIS Customizations with .NET

• SCRIPT Task (.NET) in SSIS
• Scripts for Control Flow Limitations
• Scripts for Data Flow Limitations
• ADO Connections and DataAdapters
• Expressions and Iterations

SSIS Expressions & Debugging

• Conditional Precedence Expressions
• SSIS Parameters Usage and Variables
• Control Flow Breakpoints

Data Flow Data Viewers and Audits

• Limitations of SSIS Debugging
• Debugging Procedures in Realtime
• Data Flow Transformations
• Row and Column Transformations
• Combining and splitting datasets
• Performing SQL Database operations
• MERGE Vs UNION ALL Transformations
• Lookup and Fuzzy Grouping
• SORT Vs AGGREGATE Transformations
• OLE-DB Command Transformation
• Derived Columns & Data Conversions
• Parameter Mappings & Options

SCD Transformation - ETL / DW

• Data Analytics and Profiling
• Custom Code Handling in DW
• Event Notifications and Emails
• Data Sampling and Data Delta
• Type I and Type II SCDs
• Historical Data Loads
• Inferred Members and Load IDs
• Performing PIVOT operations
• TDS Sizing and Tuning Options

Checksum Operations for ETL/DW

• CHECKSUM Transformation in DW
• CACHE Transformation & Usage
• Partial and Full Cache Options
• SSIS Transactions & Isolation Levels
• SSIS Checkpoints and Usage

Change Data Capture (CDC) for ET/DW

• CDC Sources and Usage
• CDC Controls and Logging
• Dynamic CDC Connections
• Using CDC for ETL DW Loads

Master - Child Packages & Fact Loads

• Configuring Master Packages
• Configuring Child Packages
• Parameter Bindings, Execute Process
• Scenario based Fact Loads with CDC
• Understanding ETL Requirements
• Real-world SSIS with LIVE Database
• Solutions to Data Delta Options
• Data Cleansing with Fuzzy Data

Events, Errors and Logging

• System Events and Audits
• Package Level Event Handling
• Event Bubbling Escalations
• Script Tasks & Notify Operators
• Benefit of SSIS Logging
• Windows Event Logs and SSIS
• SQL Server Logging Limitations
• Global / Project Audits and Use
• Package Execution Reports
• Logging Fuzzy Data Loads
• Data Profiler & ADO Connections
• XML Connections & BLOB Data

Deployment Procedures

• Project Builds & Options
• Creating ISPAC Files : SSIS 2014
• Creating ISPAC Files : SSIS 2012
• Securing Manifest Files
• Project Level & 64bit/32bit Errors
• Validation & Verification Techniques

SSIS Project Deployments

• SSIS Deployment & DB Engine
• SSIS Catalog Project Files
• SQL Server Security Options
• SSIS Catalog Database (SSISDB)
• Post Deployments & Connections
• Package Configurations, Parameters
• SSIS Package Validations
• Package Execution Options
• SSIS Scripting Options
• Fine-tuning Deployment
• SSIS Performance Reports

SSIS Package Security and Reports

• SSIS Package Security Options
• Encrypting SSIS Catalogs
• Package Store and SSISDB
• Security Audits and Reports
• SSIS Folder Level Security
• SSIS Project Level Security
• Container and Package Logging

SSIS Upgrades

• Project Migration Utilities
• Command-Line Deployments
• Package Backup Procedures
• Upgrading SSIS 2012 to SSIS 2014
• MSDB and SSIS Catalog Files
• Comparing SSIS 2012 & 2014
• SSIS ETL & DW - Comparisons
• SSIS ETL & DW - Conclusions