+44 20 3490 4211 | info@pradiconsulting.co.uk

Introduction to SSIS & Data Warehouse

  • Need for SSIS and ETL / DWH Entities
  • Data Warehouse Design and SSIS
  • DW Components and SSIS Tools
  • SSIS Configuration and Catalog DB
  • Control Flow Tasks Architecture
  • Data Flow Tasks and ETL Architecture
  • Data Pipelines and Data Buffers

Basic ETL Entities in SSIS

  • SSIS Data Types and Data Conversions
  • SSIS Local and Global Variables
  • Dynamic Precedence Constraints
  • Dynamic Connection Managers in DFT
  • Data Flow Transformations and Usage
  • Handling ETL Enumerations in SSIS
  • ETl with Loops & Indexed Connections

SSIS Expressions & Package Debugging

  • Conditional Precedence Expressions
  • SSIS Parameters Usage and Variables
  • Control Flow Breakpoints - Usage
  • Data Flow Data Viewers and Audits
  • SSIS Expressions &Debugging Options
  • Debugging SSIS Packages & Limitations
  • Debugging Procedures in Realtime

Checksum Operations for ETL/DW

  • CHECKSUM Transformation in DW
  • CACHE Transformation & Usage
  • Partial and Full Cache Options
  • SSIS Transactions & Isolation Levels
  • Checksum for Type 1 and Type 2
  • SSIS Checkpoints and Limitations

Change Data Capture (CDC) for ETL/DW

  • CDC Sources and Controls in SSIS
  • CDC Tables and Change Tracking
  • Dynamic CDC Connections & Logging
  • Using CDC for Dimension Loads (ETL)

SSIS Customizations with .NET

  • SSIS SCRIPT Task (.NET) and Coding
  • Scripts for Control Flow Limitations
  • Scripts for Data Flow Limitations
  • ADO Connections and Data Adapters
  • Custom Code (.NET) Handling in SSIS
  • Script Tasks & Notify Operators
  • SSIS Expressions and Iterations

SCD Transformation - ETL / DW

  • Data Analytics and Column Profiling
  • Data Sampling and Data Delta
  • Type I and Type II SCD Examples
  • Dimension Table Design, Surrogate Keys
  • SCD - Business Keys and Attributes
  • SCD - Historical Attributes and Status
  • Event Notifications and Emails
  • ETL - Partial and Full Cache Options
  • Fact Table Loads and ETL Load IDs
  • PIVOT, UNPIVOT and WebService T/Fs
  • TDS Sizing and Memory Options

Data Flow Transformations and ETL

  • Row and Column Transformations
  • Combining and Splitting Data
  • Performing SQL Database Operations
  • MERGE Vs UNION ALL Transformations
  • Lookup, Fuzzy Lookup and Grouping
  • SORT Vs AGGREGATE Transformations
  • OLE-DB Command Usage & Parameters
  • Derived Columns & Data Conversions
  • SSIS Parameter Mappings & Options
  • Tabular Data Stream & Commit Size
  • Table Locks & Batch Calculations

Events, Errors and Logging

  • SSIS Package Events and Audits
  • Package Level Event Handling Options
  • SSIS Event Bubbling Escalations
  • SSIS Logging & Project Connections
  • Windows Event Logs and SSIS Jobs
  • SQL Server Logging and Audit Tables
  • Global Connections and Project Audits
  • Logging Fuzzy Data Loads and ETL
  • Need For XML Connections & ADO
  • XML Connections & BLOB Data
  • Using SSIS Data Profile Viewer Task

Master - Child Packages & Fact Loads

  • Configuring Master Packages, Parameters
  • Configuring Child Packages, Parameters
  • Parameter Bindings, Execute Process
  • OLE DB Transformation and Parameters
  • ADO.Net Connection Limitations in SSIS
  • Solutions to Data Delta Options
  • Fact Loads with DAT / BLOB Files
  • Fact Loads with SSIS CDC Controls
  • Fact Loads with SSIS SCD Controls
  • Fact Loads with Key Lookups
  • Tuning Fact Loads in Warehouse

Deployment Procedures

  • Project Builds & Bit Level Options
  • Creating ISPAC Files : SSIS 2014, 2012
  • Securing SSIS Manifest Files (PROD)
  • 64 bit and 32 bit Configurations
  • Validation & Verification Techniques
  • Configuration Options and Connections
  • Control Loops and Indexed Connections
  • Migrating SSIS ISPAC Files & Options
  • SSIS Package Backups and Passwords
  • Project Level Passwords and Encryption

SSIS Project Deployments

  • SSIS Deployment and SSIS Catalog DB
  • SSIS Catalog Project Files and Options
  • Security - Logins, Users, SSISAdmin
  • SSIS Catalog Folders in SSISDB
  • Post Deployments & Connections
  • Package Configurations, Parameters
  • SSIS Package Validations, Reports
  • Checksum Management and Encryptions
  • SSIS Package Scripts with T-SQL
  • Tuning Deployment and Configurations
  • SSIS Package Performance Reports

SSIS Package Security and Reports

  • SSIS Package Security Options
  • Encrypting SSIS Catalog Packages
  • SSIS Package Store and SSISDB
  • Security Audits Reports and Limitations
  • SSIS Folder Level Security Options
  • Project Level Security and SSIS Roles
  • Container and Package Logging

SSIS Package Upgrades

  • SSIS Project Migration Utilities
  • Importing ISPAC and Manifest Files
  • SSIS Project Import Wizard - Usage
  • Upgrading Packages & Configurations
  • Command-Line Deployments & Options
  • Package Backup Procedures & Jobs
  • Upgrading SSIS 2012 to SSIS 2014
  • MSDB and SSIS Catalog DB Files
  • Comparing SSIS 2012 and 2014