SSIS LIVE Online Training (Integration Services with ETL & DWH)

SSIS provides Data Extraction, Consolidation and Loading Options (ETL), SQL Server Coding enhancements, Data Warehousing and Customizations for SQL Server Developers, Administrators, BI Engineers and Data Analysts. In our SSIS Training course, we deal with complete SSIS Design, Development and Deployment & Management of Various ETL Transformations, Data Loads, Data Cleansing, Incremental Data Loading Techniques including DWH Design, Management, SCD, Profiling, Tuning and Fact Table Loads. This SSIS Online Training course also includes Azure Data Factory (ADF) Pipelines and SSIS Package Migrations to ADF with Self Hosted and Integrtion Runtime ENgines, Stored Procedures Executions, Watermark Columns and Azure Data Lake with SSIS Integrations. All sessions are completely practical and realtime.

Pre-requisites: Participant needs to have knowledge on SQL Server T-SQL Queries to join our Real-time Practical Data Modelling (SSAS) Training Course.

SSIS (Integration Services) Training Plans

	PLAN A	PLAN B	PLAN C
Description	SSIS	T-SQL & SSIS	T-SQL & SSIS, ADF
Total Duration	2.5 Weeks	5.5 Weeks	8.5 Weeks
SSIS: Control Flow, Data Flow; SSIS DB	✓	✓	✓
SSIS: Static & Dynamic Connections	✓	✓	✓
SSIS: ETL, Data Warehouse Design	✓	✓	✓
SSIS: Transformations, Data Loads	✓	✓	✓
SSIS: Data Cleansing, Lookups	✓	✓	✓
SSIS: Catalog Databases, Usage	✓	✓	✓
SSIS: Deployments, Migrations	✓	✓	✓
SSIS : Star & Snowflake Schemas	✓	✓	✓
SQL : Basics, DDL, DML, SELECT	✖	✓	✓
T-SQL : Constraints, Normal Forms	✖	✓	✓
T-SQL: Joins, Group By, Queries	✖	✓	✓
T-SQL: Merge, Pivot, Date & Time Formats	✖	✓	✓
T-SQL: Excel Integrations, Pivot Charts	✖	✓	✓
ADF : Azure Data Factory	✖	✖	✓
ADF : Data Imports, ETL	✖	✖	✓
ADF : Data Flows, Wrangling	✖	✖	✓
ADF : Transformations, ETL	✖	✖	✓
Synapse: Configuration, Loads	✖	✖	✓
Synapse: ETL with ADF, DWH	✖	✖	✓
Synapse: Performance Tuning	✖	✖	✓
Synapse: MPP, cDWH, DIUs	✖	✖	✓
Total Course Fee*	INR 7000 USD 100	INR 12000 USD 150	INR 27000 USD 375

Trainer: Mr. Sai Phanindra T

S No	Time (IST, Mon - Fri)	Start Date
SQL Server & T-SQL Schedules
1	6 AM - 7 AM	Dec 5th	Register
2	8 AM - 9 AM	Dec 13th	Register
3	6 PM - 7 PM	Jan 3rd	Register
4	8 PM - 9 PM	Jan 22nd	Register
5	9 PM - 10 PM	Dec 19th	Register

S No	Time (IST, Mon - Fri)	Start Date
SSIS Training Schedules
6	8:30 AM - 9:30 AM	Dec 6th	Register

If above schedule does not work, opt for SSIS Training Videos

SSIS TRAINING HIGHLIGHTS :

✔ Basic to Adv. ETL	✔ Basic to Adv. DAX
✔ Data Modelling	✔ OLAP DB Design
✔ Power Query	✔ Basic to Adv. MDX
✔ Kimball BI Design	✔ Inmon BI Design
✔ Star, Snowflake	✔ Data Warehousing
✔ OLTP, File Data	✔ JSON, XML Data
✔ Resume Guidance	✔ MCSA MSBI Exam

All Session Are Completely Practical & Real Time

Ch 1: DATABASE INTRODUCTION

Databases Introduction & Purpose
Database Types : OLTP, DWH, OLAP
Microsoft SQL Server Advantages, Use
SQL Server Components and Usage
Microsoft SQL Server - Career Options
Developer, DBA, Data Engineer
Data Analyst, Data Scientist Careers

SQL : Purpose, Real-time Usage Options
SQL Versus Microsoft T-SQL [MSSQL]
Course Plan, Real-time Project, Resume
24 x 7 Online Lab for Remote DB Access
Versions and Editions of SQL Server
SQL Server Pre-requisites : S/W, H/W
System Configuration Checker Tool

Ch 5: SQL Basics - 3, TSQL INTRO

Database Objects : Tables and Schemas
Schemas : Group Tables in Database
Schemas : Security Management Object
Creating Schemas & Batch Concept
Using Schemas for Table Creation
Data Storage in Tables with Schemas
Data Retrieval & Usage with Schemas

Table Migrations across Schemas
Import and Export Wizard in SSMS
Data Imports with Excel File Data
Performing Bulk Operations in SSMS
Temporary Tables : Real-time Use
Local and Global Temporary Tables
# and ## Prefix, Scope of Usage

Ch 9: Functions, Procedures Basics

Functions with SQL Server, TSQL
Scalar, Inline, Table Functions
Variables: Declare, Real-time Use
Creating, Executing Functions
Functions for Computations
Functions for Parameterized Joins
Procedures: Usage in Real-time

Using Parameters in SQL Server
Parameterized Joins in TSQL
Compilation with Stored Procedures
sp_help, sp_helptext, sp_helpindex
sp_helpdb, sp_rename, sp_recompile
System Views For Metadata Audits
DBID, DBName, ObjectID, ObjectName

Ch 2: SQL SERVER INSTALLATION

SQL Server & SSMS Installation Plan
SQL Server Pre-requisites : S/W, H/W
SQL Server 2022 & 2019 Installation
Database Engine Feature, OLTP
Instances : Types and Properties
Default Instance, Named Instances
Service and Service Account Use

Authentication Modes and Logins
Windows Logins and SQL Logins
SQL Server Management Studio
Server Connections with SSMS Tool
Local and Remote Connections
System Databases: Master and Model
MSDB, TempDB, Resource Databases

Ch 6: Constraints, Index Basics

Constraints and Keys - Data Integrity
NULL, NOT NULL Property on Tables
UNIQUE KEY Constraints: Importance
PRIMARY KEY Constraint: Importance
FOREIGN KEY Constraint: Importance
REFERENCES, CHECK & DEFAULT
Candidate Keys and Identity Property

Database Diagrams and ER Models
Relationships Verification and Links
Indexes : Basic Types and Creation
Index Sorting and Search Advantages
Clustered and NonClustered Indexes
Primary Key and Unique Key Indexes
Need for Indexes - working with Keys

Ch 10: TRIGGERS & TRANSACTIONS

Triggers - Purpose, Real-world Usage
FOR/AFTER Triggers - Real time Use
INSTEAD OF Triggers - Real time Use
INSERTED, DELETED Memory Tables
Using Triggers for Data Replication
Enable Triggers and Disable Triggers
Database Level, Server Level Triggers

Transactions : Types, ACID Properties
Transaction Types and AutoCommit
EXPLICIT & IMPLICIT Transactions
COMMIT and ROLLBACK Statements
Batch Concept and Go Statement
Open Transactions in Real-time
Using Conditional Commits, Rollbacks

Ch 3: SSMS Tool, SQL BASICS - 1

Creating Databases: Files [MDF, LDF]
Creating Tables in User Interface
Data Insertion & Report in User Interface
SQL : Purpose and Real-time Usage
SQL Versus T-SQL : Basic Differences
DDL, DML, SELECT, DCL and TCL
Creating SSMS Sessions : SPID

Create, Connect Databases using SQL
Creating Tables with INT, CHAR
Data Storage, Inserts - Basic Level
Table Data Verifications with Select
SELECT Statement for Table Retrieval
Identify Databases and Tables
Identify Sessions and Session ID

Ch 7: Joins Basics, TSQL Queries

JOINS - Table Comparisons Queries
INNER JOINS For Matching Data
OUTER JOINS For (non) Match Data
Join Queries with "ON" Conditions
Left Outer Joins - Example Queries
Right Outer Joins - Example Queries
FULL Outer Joins: Realtime Scenarios

CROSS JOIN and CROSS APPLY
One-way, Two way Data Comparisons
Using Table Aliases & Column Aliases
Optimizing Join Queries with Indexes
Choosing Correct Comparison Columns
Joining Unrelated Tables in TSQL
Self References, Self Joins in TSQL

Ch 11: Normal Forms, Cursors

First Normal Form and Atomicity
Third Normal Form and MVD Property
Boycee-Codd Normal Form : BNCF
Fourth Normal Form : Advantages
Self Reference Keys and 4 NF Usage
1:1, 1:M, M:1, M:M Relationship Types
Linked Servers Configurations, RPC

Linked Servers, Remote Joins in TSQL
2 Part, 3 Part, 4 Part Naming Styles
Remote Joins Queries and Aliases
Cursors - Basics, Data Operations
Cursors - Life Cycle & Declaration
Cursors Types, FETCH Operations
Cursors - Deallocate, Real-world Use

Ch 4: SQL BASICS - 2

Creating Tables: VARCHAR, FLOAT
Single Row Inserts, Multi Row Inserts
Rules for Data Insertion Statements
SELECT with WHERE Conditions
AND and OR Operators Usage
IN Operator and NOT IN Operator
Between, Not Between Operators

LIKE and NOT LIKE Operators
ORDER BY, TOP & OFFSET
Basic Sub Queries with SELECT
UPDATE Statement & Conditions
DELETE & TRUNCATE Statements
ALTER, ADD COLUMN Statements
DROP Statements: Table, Database

Ch 8: Group By in TSQL, Views Basics

GROUP BY: Importance, Realtime Use
GROUP BY Queries and Aggregations
Group By Queries with Having Clause
Group By Queries with Where Clause
Using WHERE and HAVING in T-SQL
Group By with Joins in TSQL
Query Execution Order & Aliases

Joins with Sub Queries, Formatting
Database Objects: Overview & Usage
Views: Types, Usage in Real-time
Creating, Executing & Verifying Views
Storing Queries in Database Views
Excel Analytics - Joins & Views
Excel Office Data Connection Reports

Ch 12: TSQL Queries, SQL Analytics

IIF() Function with SELECT Query
WHEN..THEN..ELSE
WHEN MATCHED, NOT MATCHED
Incremental Loads, Upsert Statement
Stored Procedures: Merge Statement
UNION and UNION ALL Operator
Window Functions: Rank, Dense Rank

Row_Number, PartitionBy in TSQL
Duplicate Row Identification, Deletion
Grouping, Cube, Rollup, Lag, Lead
Data Types: Numerical, Date, Time
Data Types: Characters, Real, Float
Date & Time Functions, DateAdd
String Functions, Concat, SubString

Case Study 1: Database Design with Tables,
Constraints, Keys & Relations

Case Study 2: Joins with Group By,
Sub Queries, Views, Excel Analytics

Module 2 : SSIS [Applicable for SSIS Plans A, B, C]

Module I: Basic SSIS Development & ETL - DWH	Module II: Advanced SSIS Development & Deployments
Ch 1: SSIS INTRO, INSTALLATION Integration Services (SSIS) & ETL / DWH SSIS for Data Loads, ETL, Warehouse SSDT : SQL Server Data Tools SSIS Development, LIVE (Deployment) Data Warehouse Design & ETL Process DWH and ETL Structures Implementation SSIS ETL for Data Reads, Data Cleansing Data Warehouse (DWH) Design Principles SSIS 2019, 2017 : SSIS DB Installations SSIS Database, Catalog Folders, Storage SSIS Catalog Database (SSIS DB) Creation SQL Server Data Tools - SSDT / Visual Studio SSDT Installation and Catalog Verification SSIS, ETL, DWH, Data Flow, Data Buffer SSIS Package Environment, SSDT Projects SSIS & ETL Training - Lab Plan, Resources	Ch 7: Checksum & DWH Design Checksum Transformation in ETL Loads Configuring Checksum: SSIS 2019, 2017 Transformation Logic, Parity Checks CRC Checksum For Type I, Type II ETL DWH DWH Dimension Tables With Checksum Lookup Transformation, Row Redirection OLE DB Command and Input Parameters Parameter Mapping, Dynamic Updates Cache Transformation CAW Memory Files Memory Connection Lookup with Cache Tuning Lookup: Caching, Index Options Pre-ETL Activities, DB Recovery Models FULL/ PARTIAL CACHE & NOCACHE Performance Tuning and Pre-ETL Loads Dependent Data Flow Tasks, Post ETL Internal Parameters and Query Updates Cache Allocation Options with ETL DWH
Ch 2: SSIS ETL PACKAGES: BASICS Control Flow Tasks Architecture, Purpose Data Flow Tasks Architecture, Purpose SSIS Packages @ Basic Data Flow, ETL SSIS Projects and Package Creation Data Pipelines in Data Flow Tasks SSIS Packages Execution Process Data Flow Objects, OLE DB Connections SSIS Package Creation - Control Flow DTSX Files for Package Execution SSIS Execution, Package Errors & Logs SSIS Transformation: Conditional Split Excel Connection and Memory Reference Source and Destination Assistants DAT File Imports and Annotations SSIS Project Configurations, Debugging SSIS 64 Bit, 32 Bit Configurations	Ch 8: CDC Transformations for DWH DML Audits with Change Data Capture CDC Tables with SQL Server Connections CDC Connections & ADO.NET Integration CDC Control Flow and CDC State Values INITIAL LOADS & PRCESSING RANGE State Variables, Net Changes, Logging Initial & Incremental Dimension Loads Dynamic CDC Control, OLEDB Command Internal Parameters and Usage Options Parameter Mapping For ETL Type1, Type2 Integrating Control Flow for CDC @ ETL CDC Splitter - Row Inserts, Updates CDC Precautions, Input & Output Range Derived Column Transformations with CDC Limitations of ADO.NET Connections Master Child Packages,Parameter Binding Package Passwords, Project Parameters Project Configuration Options(32,64 bit)
Ch 3: MERGE & FUZZY LOOKUP MERGE and UNION ALL Transformations SORT, NOSORT and Advanced Sort Synchronous, Asynchronous Tfns Row, Partial Blocking Transformations Fully Blocking Transformations - Buffers Avoiding Fully Blocking Transformation Bulk Load Operations, SSIS Data Imports IsSorted & SortKey Position Options SSIS Package Performance & Resources Data Conversion Expressions Fuzzy Lookup Transformation, References Nomatch Cleansing @ Conditional Split Index Creations, Lookup Transformation Data Conversion, Derived Columns Varchar, Nvarchar, Error Redirections Threshold Values, Search Delimiters _Similarity, _Confidence Columns	Ch 9: Fact Table Design, DWH Loads Fact Table - Design and ER Model DWH : STAR & SNOWFLAKE Schemas Time Dimensions and ETL Date / Time Link Time Dimension to Facts, Lookups Parent-Child Packages for Fact Loads Inferred Members for NULL Dimensions SCD Wizard for DWH Fact Table Design Parameter Mapping for Incr Updates ETL Load IDs - Dimension Attributes Error Handling, Event Handling in SSIS Text Qualifiers with Flat File Sources Fact Load Design for Initial, Incr Loads End-to-End DWH Design Implementation Direct Data Loads and Staging Tables Fact Table Staging and Incr Updates
Ch 4: SSIS CHECKPOINT & PIVOT Execute SQL Task and OLE DB Queries Transaction Options For SSIS Executables Precedence - Success/Failure/Completion SSIS Package Rollbacks Execution Options Checkpoints Purpose with Data Flow Tasks Checkpoint Files and SSIS Logging Tasks Transactions with Checkpoint File in SSIS Checkpoint Option Advantages, Limitations FailPackageOnFailure, Checkpoint Property REQUIRED/SUPPORT/ NOTSUPPORTED Transaction Property, CHECKPOINT Files Legacy Data, Data Cleansing, Formatting Denormalization, Keys. Need for OLTP PIVOT Transformation, Connection Assistant Pivot Usage - Implementation. Key Values Lineage ID - Purpose. Data Mappings Lineage IDs for Column Mapping, Pivot Keys SSIS Input Columns and Mappings Data Viewer : Data Transfer Verification Data Type Conversions, Error Redirection	Ch 10: DWH MIGRATIONS, SCRIPT Task DataWarehouse Migrations with SSIS Using SSIS Containers, Db Integrity Task Pre-Database Migration Task Precautions Online and Offline DB Transfer Options Copy / Move with DWH Migrations SMTP : Simple Mail Transfer Protocol SQL Server Agent and Package Events Data Profiling with ADO.NET Connectors XML Files & SSIS Data Profiler Tool Script Task - Working in SSIS Control Flow Script Task - VB.NET Program Compilations Variables, Parameters with SSIS Script Task Read Only, Read Write Variable Expressions Expressions and Debugging, Break Points Variables, Parameters Mapping Expressions File System Tasks and Limitations SQLDataAdapters & System.Data.SQLClient
Ch 5: EVENTS, LOOPS, EXPRESSIONS SSIS Package Events, Validation, Execution PreExecution, Progress, Cleanup Events SSIS Events, Errors/Warnings/Information Configuring sysssislog System Tables Debugging : Data Viewers and Breakpoints ForEach Loop Container. File Connections Variables For Linking DFT, Control Flow Dynamic Connections with Variables Iterations, Fetch, Index Mapping SSIS Expressions for ETL and DWH FOR LOOP Expressions in SSIS Init/EvalExpression, AssignExpression SSIS Expressions, Functions, Values Data Insertions, Data Serializations Counter Values, Variables & Parameters SSIS for OS Level Operations, Loops Execute SQL Task : Return Values	Ch 11: SSIS DEPLOYMENTS, UPGRADES SSISDB Catalog Deployments, ISPAC Files Package Builds, Verification, Scripts Project Deployment Wizard Targets, Logs DB Catalog Folders & Projects Creation Package Executions - Scripts, Reports Package Validations, 32/64 bit Options Configurations & Parameter Management Package Jobs @ SQL Agent. Job Steps Job Schedules and Notifications Event Logs Package Security - SSISDB Logins, Users Folder Level and Project Level Security Project Migration Utilities, Upgrades Package Imports, Exports with ISPAC Files Command-Line Deployment,Execution Utility Package Execution & Validation Reports Package Versions and Restores, Rollbacks
Ch 6: SSIS with ETL, Warehouse (DWH) OLTP Database : Historical Data Loads Data Warehouse (DWH) Purpose, Usage Dimensions, Attributes, Members Types Dimension Tables, Fact Tables Design TYPE1 and TYPE2 ETL Implementation SCD Type1, Type 2 for DWH in Sales Inferred Members and Legacy Loads Initial Data Loads with Data Marts Business Keys & non Identity Columns Surrogate Keys, Alternate Business Keys Cascading OLTP / Stage to DWH Rows Fixed Attributes, Changing Attributes Historical Attributes. Inferred Updates ETL Date, Row Status Transformations Attribute Key Types in SCD, Limitations Historical Attributes and Data Delta SSIS Connection Assistants - Reuse SCD Transformations in Real-time	Ch 12: SSIS PROJECT [ECommerce Domain] SSIS ETL & DWH with STAR Schema SSIS ETL & DWH with SNOWFLAKE Schema Dimension Table Design & Business Keys Fact Table Design & Data Integrity Incremental Data Loads with Containers Key Lookup and Business Key Identification Staging Tables, Master - Child Packages SSIS Package Deployment [Catalog Deployments] SSIS Training : Project Specifications Project Explanation and Solution Project FAQs and Resume Support Chapter Wise FAQs and Answers Concept Wise Tasks and Solutions SSIS DWH Databases with Reports Technical Resume Points in SSIS SSIS Limitations and Need for ADF End to End Implementation [MSBI]

Module I: Basic SSIS Development & ETL - DWH

Module II: Advanced SSIS Development & Deployments

Ch 1: SSIS INTRO, INSTALLATION

Integration Services (SSIS) & ETL / DWH
SSIS for Data Loads, ETL, Warehouse
SSDT : SQL Server Data Tools
SSIS Development, LIVE (Deployment)
Data Warehouse Design & ETL Process
DWH and ETL Structures Implementation
SSIS ETL for Data Reads, Data Cleansing
Data Warehouse (DWH) Design Principles
SSIS 2019, 2017 : SSIS DB Installations

SSIS Database, Catalog Folders, Storage
SSIS Catalog Database (SSIS DB) Creation
SQL Server Data Tools - SSDT / Visual Studio
SSDT Installation and Catalog Verification
SSIS, ETL, DWH, Data Flow, Data Buffer
SSIS Package Environment, SSDT Projects
SSIS & ETL Training - Lab Plan, Resources

Ch 7: Checksum & DWH Design

Checksum Transformation in ETL Loads
Configuring Checksum: SSIS 2019, 2017
Transformation Logic, Parity Checks CRC
Checksum For Type I, Type II ETL DWH
DWH Dimension Tables With Checksum
Lookup Transformation, Row Redirection
OLE DB Command and Input Parameters
Parameter Mapping, Dynamic Updates

Cache Transformation CAW Memory Files
Memory Connection Lookup with Cache
Tuning Lookup: Caching, Index Options
Pre-ETL Activities, DB Recovery Models
FULL/ PARTIAL CACHE & NOCACHE
Performance Tuning and Pre-ETL Loads
Dependent Data Flow Tasks, Post ETL
Internal Parameters and Query Updates
Cache Allocation Options with ETL DWH

Ch 2: SSIS ETL PACKAGES: BASICS

Control Flow Tasks Architecture, Purpose
Data Flow Tasks Architecture, Purpose
SSIS Packages @ Basic Data Flow, ETL
SSIS Projects and Package Creation
Data Pipelines in Data Flow Tasks
SSIS Packages Execution Process
Data Flow Objects, OLE DB Connections
SSIS Package Creation - Control Flow

DTSX Files for Package Execution
SSIS Execution, Package Errors & Logs
SSIS Transformation: Conditional Split
Excel Connection and Memory Reference
Source and Destination Assistants
DAT File Imports and Annotations
SSIS Project Configurations, Debugging
SSIS 64 Bit, 32 Bit Configurations

Ch 8: CDC Transformations for DWH

DML Audits with Change Data Capture
CDC Tables with SQL Server Connections
CDC Connections & ADO.NET Integration
CDC Control Flow and CDC State Values
INITIAL LOADS & PRCESSING RANGE
State Variables, Net Changes, Logging
Initial & Incremental Dimension Loads
Dynamic CDC Control, OLEDB Command
Internal Parameters and Usage Options

Parameter Mapping For ETL Type1, Type2
Integrating Control Flow for CDC @ ETL
CDC Splitter - Row Inserts, Updates
CDC Precautions, Input & Output Range
Derived Column Transformations with CDC
Limitations of ADO.NET Connections
Master Child Packages,Parameter Binding
Package Passwords, Project Parameters
Project Configuration Options(32,64 bit)

Ch 3: MERGE & FUZZY LOOKUP

MERGE and UNION ALL Transformations
SORT, NOSORT and Advanced Sort
Synchronous, Asynchronous Tfns
Row, Partial Blocking Transformations
Fully Blocking Transformations - Buffers
Avoiding Fully Blocking Transformation
Bulk Load Operations, SSIS Data Imports
IsSorted & SortKey Position Options
SSIS Package Performance & Resources

Data Conversion Expressions
Fuzzy Lookup Transformation, References
Nomatch Cleansing @ Conditional Split
Index Creations, Lookup Transformation
Data Conversion, Derived Columns
Varchar, Nvarchar, Error Redirections
Threshold Values, Search Delimiters
_Similarity, _Confidence Columns

Ch 9: Fact Table Design, DWH Loads

Fact Table - Design and ER Model
DWH : STAR & SNOWFLAKE Schemas
Time Dimensions and ETL Date / Time
Link Time Dimension to Facts, Lookups
Parent-Child Packages for Fact Loads
Inferred Members for NULL Dimensions
SCD Wizard for DWH Fact Table Design

Parameter Mapping for Incr Updates
ETL Load IDs - Dimension Attributes
Error Handling, Event Handling in SSIS
Text Qualifiers with Flat File Sources
Fact Load Design for Initial, Incr Loads
End-to-End DWH Design Implementation
Direct Data Loads and Staging Tables
Fact Table Staging and Incr Updates

Ch 4: SSIS CHECKPOINT & PIVOT

Execute SQL Task and OLE DB Queries
Transaction Options For SSIS Executables
Precedence - Success/Failure/Completion
SSIS Package Rollbacks Execution Options
Checkpoints Purpose with Data Flow Tasks
Checkpoint Files and SSIS Logging Tasks
Transactions with Checkpoint File in SSIS
Checkpoint Option Advantages, Limitations
FailPackageOnFailure, Checkpoint Property
REQUIRED/SUPPORT/ NOTSUPPORTED
Transaction Property, CHECKPOINT Files

Legacy Data, Data Cleansing, Formatting
Denormalization, Keys. Need for OLTP
PIVOT Transformation, Connection Assistant
Pivot Usage - Implementation. Key Values
Lineage ID - Purpose. Data Mappings
Lineage IDs for Column Mapping, Pivot Keys
SSIS Input Columns and Mappings
Data Viewer : Data Transfer Verification
Data Type Conversions, Error Redirection

Ch 10: DWH MIGRATIONS, SCRIPT Task

DataWarehouse Migrations with SSIS
Using SSIS Containers, Db Integrity Task
Pre-Database Migration Task Precautions
Online and Offline DB Transfer Options
Copy / Move with DWH Migrations
SMTP : Simple Mail Transfer Protocol
SQL Server Agent and Package Events
Data Profiling with ADO.NET Connectors

XML Files & SSIS Data Profiler Tool
Script Task - Working in SSIS Control Flow
Script Task - VB.NET Program Compilations
Variables, Parameters with SSIS Script Task
Read Only, Read Write Variable Expressions
Expressions and Debugging, Break Points
Variables, Parameters Mapping Expressions
File System Tasks and Limitations
SQLDataAdapters & System.Data.SQLClient

Ch 5: EVENTS, LOOPS, EXPRESSIONS

SSIS Package Events, Validation, Execution
PreExecution, Progress, Cleanup Events
SSIS Events, Errors/Warnings/Information
Configuring sysssislog System Tables
Debugging : Data Viewers and Breakpoints
ForEach Loop Container. File Connections
Variables For Linking DFT, Control Flow
Dynamic Connections with Variables
Iterations, Fetch, Index Mapping

SSIS Expressions for ETL and DWH
FOR LOOP Expressions in SSIS
Init/EvalExpression, AssignExpression
SSIS Expressions, Functions, Values
Data Insertions, Data Serializations
Counter Values, Variables & Parameters
SSIS for OS Level Operations, Loops
Execute SQL Task : Return Values

Ch 11: SSIS DEPLOYMENTS, UPGRADES

SSISDB Catalog Deployments, ISPAC Files
Package Builds, Verification, Scripts
Project Deployment Wizard Targets, Logs
DB Catalog Folders & Projects Creation
Package Executions - Scripts, Reports
Package Validations, 32/64 bit Options
Configurations & Parameter Management
Package Jobs @ SQL Agent. Job Steps

Job Schedules and Notifications Event Logs
Package Security - SSISDB Logins, Users
Folder Level and Project Level Security
Project Migration Utilities, Upgrades
Package Imports, Exports with ISPAC Files
Command-Line Deployment,Execution Utility
Package Execution & Validation Reports
Package Versions and Restores, Rollbacks

Ch 6: SSIS with ETL, Warehouse (DWH)

OLTP Database : Historical Data Loads
Data Warehouse (DWH) Purpose, Usage
Dimensions, Attributes, Members Types
Dimension Tables, Fact Tables Design
TYPE1 and TYPE2 ETL Implementation
SCD Type1, Type 2 for DWH in Sales
Inferred Members and Legacy Loads
Initial Data Loads with Data Marts
Business Keys & non Identity Columns

Surrogate Keys, Alternate Business Keys
Cascading OLTP / Stage to DWH Rows
Fixed Attributes, Changing Attributes
Historical Attributes. Inferred Updates
ETL Date, Row Status Transformations
Attribute Key Types in SCD, Limitations
Historical Attributes and Data Delta
SSIS Connection Assistants - Reuse
SCD Transformations in Real-time

Ch 12: SSIS PROJECT [ECommerce Domain]

SSIS ETL & DWH with STAR Schema
SSIS ETL & DWH with SNOWFLAKE Schema
Dimension Table Design & Business Keys
Fact Table Design & Data Integrity
Incremental Data Loads with Containers
Key Lookup and Business Key Identification
Staging Tables, Master - Child Packages
SSIS Package Deployment [Catalog Deployments]

SSIS Training : Project Specifications
Project Explanation and Solution
Project FAQs and Resume Support
Chapter Wise FAQs and Answers
Concept Wise Tasks and Solutions
SSIS DWH Databases with Reports
Technical Resume Points in SSIS
SSIS Limitations and Need for ADF
End to End Implementation [MSBI]

* Above curriculum applicable for registrations from Feb 10th, 2020

Module 3 : Azure Data Factory [Applicable for SSIS Plans C]

Module 1	Module 2	Module 3
Chapter 1: Azure Basics, Azure SQL DB Cloud Introduction and Azure Basics Azure Implementation: IaaS, PaaS, SaaS Benefits of Azure Cloud Environment Azure Data Engineer: Job Roles Azure Storage Components Azure ETL & Streaming Components Need for Azure Data Factory (ADF) Need for Azure Synapse Analytics Azure Resources and Resource Types Resource Groups in Azure Portal Azure SQL Server [Logical Server] Firewall Rules and Azure Services Connections with SSMS & ADS Tools DB Migration: OnPremise to Azure Schema Migration & Data Migration	Chapter 5: OnPremise Data with ADF On-Premise Data Sources with Azure Self Hosted Integration Runtime (IR) Access Keys, Remote Linked Services Synapse SQL Pool (DW) with OnPremise Staged Data Copy and Performance Pipeline Executions and Monitoring Pipeline RunIDs and Audits / Tracing Incompatible Rows Skips, Fault Tolerance Incremental Loads with Files (BLOB) Pipeline Executions and Schedules Regular Schedules and Tumbling Window Execution Retry and Delay Options Binary Copy, Last Modified Date in Blob Automated Loops and Trigger Schedules Incremental Loads Verification Tests	Chapter 9: Synapse Analytics with Spark Apache Spark Pool in Azure Synapse Spark Cluster Nodes: Vcores, Memory Creating Spark Clusters @ Synapse Studio Python Notebooks For Remote Access Creating Databases in Apache Spark Pool Data Loads from Dedicated SQL Pools Table Creations, Aggregation Operations PySpark Code for Data Operations, Writes Serverless Pool in Azure Synapse Connections, Usage with Serverless Pool Using Azure OpenDatasets in Synapse OPENROWSET and BULK Data Loads Azure Storage Account : Data Analysis Working with Parquet Files in Synapse Python Notebooks (Pyspark) in Synapse
Chapter 2: Synapse SQL Pools (DWH) Dedicated SQL Pools in Azure Enterprise Data Warehouse with Synapse DWU: Data Warehouse Units, Resources Massively Parallel Processing (MPP) Control Nodes and Compute Nodes SQL Pool Access from SSMS Tool T-SQL Queries @ SQL Pools Start/Resume/Pause, Scaling Options Creating Tables in Azure SQL Pool Compression, MAX DOP & Indexes Distributions: Round Robin, Hash Distributions: Replicate and Usage Data Imports with COPY Table Dynamic Views (DMV) with PDW Data Loads Monitoring, Resource Class	Chapter 6: ADF Data Flow - 1 Limitations with Copy Data Tool Data Flow Task, Data Flow Activity Transformations with Data Flow Spark Cluster For Debugging Cluster Node Configurations Data Preview Options with DFT SELECT Transformation & Options JOIN Transformation and Usage Conditional Split Transformation Aggregate & Group By Transformations Synapse Sink Options with DFT DFT Optimization Techniques Pipeline Debug Runs and ETL Testing Spark Cluster For Pipeline Executions Pipeline Monitoring & Run IDs	Chapter 10: Incremental Loads @ Synapse Incremental Loads with Synapse Studio Multi Table Merge Operations On-Premise Data Sources & Timestamps Azure SQL DB Destinations, Watermarks Watermark Table Usage & Audits Stored Procedures for Timestamp Updates Table Data Type and Dynamic MERGE SQL Queries for Datasets and Fetch Lookup Activity and its Usage un Synapse Expressions in ADF Portal for Lookup Expressions in ADF Portal for Source Output Pipeline Expression, Data Window Concat Function, Run IDs Expressions JSON Parameters, Pipeline Scheduling Pipeline Validation, Trigger and Monitoring
Chapter 3: Azure Data Factory Concepts Azure Data Factory (ADF) Concepts Hybrid Data Integration at Scale ADF Pipeline Components & Usage Configure ADF Resource in Azure Understanding ADF Portal and IR Linked Services and Connections Datasets and Tables / Files for ETL ADF Pipelines: Design, Publish & Trigger ADF Pipeline with Copy Data Tool Creating Azure Storage Account Storage Container, BLOB File Uploads Data Loads with Azure BLOB Files DIU Allocations and Concurrency Creating Linked Services, Datasets Pipeline Trigger, Author and Monitor	Chapter 7: ADF Data Flow - 2 ADF Pipelines For ETL Operations Data Flow Tasks and Activities in Synapse Pivot Transformation For Normalization Generating Pivot Column, Aggregations Pivot Transformation and Pivot Settings Pivot Key Selection, Value and Nulls Pivoted Columns and Column Pattern Column Prefix, Help Graphic & Metadata Window Functions & Usage in Data Flow Rank / DenseRank / Row Number Over Clause and Input Options Derived Column Transformations Exists & Lookup Transformations Reusing Data Flow Tasks in Synapse Pipeline Validations & Executions	Chapter 11: Optimizations, Power Query ADF ETL with GUI : Power Query Power Query Resoruce Creation, Use Source Data Configurations & Settings Rename, Remove, Pivot, Group By, Order Index, Filter, Remove Error Rows Using Power Query Activity, ADF Pipelines Spark Cluster Configurations for Pipelines Concurrency, Big Data Recommendations Storage Optimization Techniques ETL Optimization Techniques SQL Pool (Synapse) Optimizations Indexes, Partitions, Distributions, DOP Pipeline Optimization Techniques Partitions, DOCP, Compressions, DIU Staging, Polybase and Core Counts
Chapter 4: ADF Pipelines, Polybase Copy Data Tool For ETL Operations Azure SQL DB to Synapse Data Loads Working with Multi Tables Data Loads Query Options for Source Datasets Transformations with Copy Data Tool Rename, Rearrange & Remove Options Pipeline Execution: DTU & DOCP ADF Pipeline Monitoring Options ADF Pipelines: Execution Settings ADF Logging Options & Storage Account Compression Option, DOP and DOCP ETL Staging Advantages & Performance Staging with Storage Account, Container ADF Pipeline Triggers and Monitoring Polybase For Azure Synapse, Advantages	Chapter 8: Azure Synapse Analytics Azure Synapse Analytics Resource Azure Synapse Analytics Workspace Managed Resource Group, SQL Account SQL Admin Account and its Purpose Operations with Synapse Workspace ADLS Gen 2 Storage Account, Container Synapse Studio (Synapse Portal) Dedicated SQL Pools & Spark Pools Creating Dedicated SQL Pools Synapse Tables, Data Loads with T-SQL COPY INTO Statements with T-SQL Clustered Column Store Indexes Row Terminator and Compressions T-SQL Queries and Aggregations Aggregation Data Loads in Synapse	Chapter 12: Pipeline Monitoring, Security Azure Monitor Resource and Usage Pipeline Monitoring Techniques ADF: Pipeline Monitoring and Alerts Synapse: Pipeline Monitoring and Alerts Synapse: Storage Monitoring and Alerts Conditions, Signal Rules and Metrics Email Notifications with Azure Concurrency, Big Data Recommendations Azure Active Directory (AAD) Users, Groups IAM: Identity & Access Management Synapse Workspace Security with RBAC ADF Security with RBAC: Owner, Contributor Azure Synapse SQL Pool Security: Logins Users, Roles and Resource Classes (RC) Assigning RCs to Users. Object Level Security