Azure SSIS Training Course

Azure Azure SSIS Online Training course with 100% HandsOn. Course includes Azure SSIS IR, Provisioning SSISDB in Azure SQL Server, Using Azure SSIS Feature Pack, Blob Connections, SQL DB and Azure SQL DW Connections in SSIS. Also using ADF Integrations, Azure File System Deployments and Azure SSIS Migration Considerations. Includes Elastic Jobs, History Retention Schemas, Azure SSISB Package Executions, Audit Reports, Versions and Security.

This Azure SSIS Online Training course also includes best practices on Migrations, Azure SSIS Web Activity in ADF for IR Scheduling, Lift & Shift Considerations, SSIS Package Executions from ADF as well as from Visual Studio with ADF Account. Register Today for Free Demo

Azure SSIS Training Plans (with Azure Data Factory, Synapse)

	PLAN A	PLAN B	PLAN C	PLAN D
Course includes	Azure SSIS	ADF, Synapse, Azure SSIS	SSIS, ADF, Synapse, Azure SSIS	T-SQL, SSIS, ADF, Synapse, Azure SSIS
Total Duration	1 Week	3 Weeks	5 Weeks	7 Weeks
Azure SSIS: Migrations to ADF	✔	✔	✔	✔
Azure SSIS: IR Schedules, Versioning	✔	✔	✔	✔
Azure SSIS: Elastic Jobs, Adf Jobs	✔	✔	✔	✔
ADF: Pipelines, Activities, IR, DIU	❌	✔	✔	✔
ADF: Data Flow & Data Wrangling	❌	✔	✔	✔
Synapse: MPP Architecture, DWUs	❌	✔	✔	✔
Synapse: Data Loads, CTAS @ T-SQL	❌	✔	✔	✔
SSIS: ETL Concepts, Transformations	❌	❌	✔	✔
SSIS: SCD & CDC for DWH Design	❌	❌	✔	✔
SSIS: Fact Loads, SSDB Catalog DB	❌	❌	✔	✔
T-SQL: Db Basics, SQL Queries, Constraints	❌	❌	❌	✔
T-SQL: Joins, Queries, Parameters, Locks	❌	❌	❌	✔
Course Fee	INR 5000 USD 68	INR 14000 USD 190	INR 20000 USD 271	INR 24000 USD 325

Trainer & Profile : Mr. Sai Phanindra T

S. No	Timings (IST)	Start Date
Azure SSIS Training Schedules
1	6 AM - 8 AM (w)	Oct 3rd	Register
2	8 PM - 10 PM (W)	OCt 17th	Register

If above schedules does not work for you, please register for Azure SSIS Training Videos

Azure SSIS Training Highlights

Azure Migrations	SSIS Migrations
Big Data Sources	IR Config
Elastic Jobs	ADF Jobs
ADLS Sources	Synapse DWH
MPP, DWUc	CTAS, T-SQL

ALL SESSIONS ARE COMPLETELY PRACTICAL & REAL-TIME

Register Today for Free Demo

Azure SSIS Training Course Contents:

Curriculum

Register Today

Module 1: Azure SSIS Training Content

Chapter 1: SSIS Migrations to Azure Data Factory
Provision Azure SQL Server Integration Services; SSIS DB Creation and Configuration Recommendations; Node Size, Node Number and Edition / License Options; Count, Parallel Execution Settings and DIUs; Edition Settings, logical SQL Server Configurations; SSIS DB Access from SSMS, SKU & Edition Checks; SSIS Package Validations with Connection Parameters; Generation of ISPAC Files in On-Premise, Validations; SSIS Catalog Database, Catalog Folders Creation; SSIS Deployments with ISPAC to Azure SSIS Catalog DB; Package Verifications and Acess from ADF Portal; SSIS Package Execution from ADF Portal, SSIS Store;

Chapter 2: Azure SSIS from Visual Studio (SSDT)
Accessing Azure Data Factory Resource from SSDT; Storage Account and BLOB Container Configurations; Azure SQL Server Deployment for SSIS Catalog DB; Configuring Azure Data Factory, Azure SSIS IR; Using Azure Enabled Integration Services Project in SSDT; Enabling, Linking ADF Account to SSIS Project; Using Azure BLOB Upload Task in SSIS - SSDT Tool; Storage Account, Access Key & Authentication Tests; SSIS Import and Export Operations with Azure; Excel Sheet to Azure SQL Database Tables in SSIS; Connection Settings and Package Executions in Azure; PIVOT Operations in SSIS, Data Uploads to Azure;

Chapter 3: SSIS Packages with Azure Feature Pack
Azure Feature Pack Installations and Real-time Usage; Using Azure BLOB Destination with ETL in SSIS; Using Azure SQL Server Databases with ETL in SSIS; SSIS Package Design with Azure Connections, Parameters; Azure SSIS Runtime Configurations, SSIS DB usage; Azure SSIS Package Executions with T-SQL Scripts; Real-time Considerations with Azure SSIS Migrations; Security Options - Data Security at Rest, TDE Options; Tools - Azure SSIS IR Customizations and Precautions; Pricing - Azure Hybrid Benefit (AHB), DIU and DTU Costs; 3rd Party Tools - Azure SSIS Feature Pack, IR Customization; Networking - Odata Sources, WAN & VNET Considerations;

Chapter 4: Elastic Jobs with Azure SSIS DB
Working with Azure SSIS DB - Standard Edition; Elastic Job Agent Service and Job Databases; Creating Master Keys and Database Credentials; Job Database Configurations with T-SQL Scripts; Testing Elastic Jobs from Azure Portal, T-SQL; Setting Retention Window in Azure SSIS DB with T-SQL; Start, Stop & Schedule Azure SSIS IR; Working with Web Activity in Azure Data Factory; Web Activity : PUSH / PULL Settings, Messages; Azure Management URLs, Azure SSIS IR Start & Stop; MSBI Authentication with Web Activity, Security; Execute SSIS Package Activity with ADF Pipeline; ADF Trigger Schedules and Tumbling Window;

Chapter 5: Azure SSIS Projects : Versioning, ADF Jobs
Azure SSIS Projects : Deployments and Exports; Azure SSIS Projects : Improts to SSDT Environment; Enabling ADF for Existing SSIS Projects, Parameters; SSIS Package Deployments. Deployment Rollbacks, LSN; Creating ADF Jobs from SSMS - using SSIS Schedules; Trigger Edits and Configuration Settings with ADF Jobs; Azure SSIS - Security Management; Creating Logins and Users in Azure SQL Server; User Mapping with SSIS Catalog Database, Scripts; Folder Level and Project Level Permissions in SSSDB; Read, Modify and Manage Permissions in SSIS DB; Principal IDs and Permission Types, Object Types; IAM : ADF Security @ Azure Active Directory Users, Groups;

Module 2: Azure Data Factory & Synapse Training Content

1. Azure Fundamentals: What is Cloud? Advantages of Azure Cloud? IaaS, SaaS & PaaS. Azure Data Engineer Technologies, Job Roles. DP 200, 201 Exams Azure Account Registration and Free Trail Activation; Understanding Azure Resources and Resource Types; Creating Resource Groups in Azure Portal;
2. Azure SQL Server: Azure Resources; Resource Groups; Azure SQL Server [Logical Server] Creation; Server Name Format and Firewall Rules; Azure Services Access with Firewall; Test Connections with SSMS Tool and Azure Data Studio Tool; Creating Azure SQL Databases in Portal, T-SQL; Tables, Data Inserts;
3. Azure SQL DB Migrations: Azure SQL DB Migrations from OnPremise; Using Data Migration Assistant Tool; Migration Assessments; Deploy Schema & Migrate Data Options; Onpremise Versus Azure SQL DB Differences; Generating bacpac Files From SSMS Tool; Azure SQL DB Exports & Imports;

Module 1	Module 2	Module 3
Ch 1: Azure Data Factory, Synapse Intro Azure Data Factory (ADF) Operations Hybrid Data Ingestion, Orchestration Data Processing & Movement in ADF Data Pipelines, Flows & Wrangling Data Mashup and ETL in Azure Azure Synapse (Data Warehouse) Enterprise Warehouse with Synapse Azure Synapse (SQL Pools) Creation DWUs: Data Warehouse Units Usage Big Data Storage and Data Analytics Column Store Data in Azure Synapse Automated Tuning and Security Access, Pause/Resume with Synapse SSMS & ADS Tools Connections	Ch 4: Azure Data Lake with ADF Creating Azure Data Lake Storage Data Lake Gen 2 Hierarchial Namespace Excel Upload to Container, Data Preview Pipeline Parameters, Variables, OUT Copy Data Tool: Timeout and Schedule Secured Pipelines and Linked Services Sink Options; Colum Mapping, Triggers Azure SQL Database Loads to Synapse Azure SQL Database Tables Data Loads For Each Loops with ADF Pipelines Copy Data Tool, Pipeline Edits in ADF Task Schedules and Tumbling Window Pipeline Execution & Runs; Monitor	Ch 7: Incremental Loads with ADF - 2 Incremental Load Pipeline Design in ADF Working with Azure Storage Containers Pipeline Executions, Incremental Schedules Regular Schedules & Tumbiling Windows Binay Copy, Last Modified Date in Blob Pipeline Trigger Schedules, Modifications Incompatable Rows Skips, Fault Tolerance Incremental Loads with Mutliple Tables Stored Procedures, Loops in ADF Pipelines Configure ETL Sources, Pre-Copy Scripts Using @{item() with Dyanamic Connections Table_Schema for Column Mapping Writing Expressions For Dynamic Loads Staging and Performance for ADF Loads
Ch 2: Azure Synapse Architecture MPP - Massively Parallel Processsing Control Node and Compute Nodes Azure Storage, DMS and DWUs Round Robin, Replicate, Hash Tables Service Level Objective, Sharding Resource Classes; Gen 1 and Gen 2; Table Creation, Storage, Distribution CTAS: Create Table As Select. Indexes Distribution Types, Time Partitions Logins and Users in SQL Server Users and Roles in Synapse SQL DW Resource Classes; Blob Data Import COPY INTO Statement in T-SQL Data Monitoring Scripts with T-SQL	Ch 5: On-Premise Data to Azure On-Premise Data Sources with Azure Install Self Hosted Integration Runtime Access Keys & Use. Configuration Tools Remote Linked Services in ADF & SH IR Authentication with Integration Runtime Sourc, Sink Linked Service Connections Incompatable Rows Skip, Fault Tolerance Table Mapping, Column Mapping, Errors Synapse Pool Connection with Onpremise Staged Data Copy & ETL Performance Azure Blob for Staging. Polybase Connections Management - Preview Pipeline Exectution, Run IDs, Errors	Ch 8: Mapping Data Flow in ADF Data Flow Task Creation in ADF Pipelines Transformation Editor and Parameters Comparing ADF Pipelines and Data Flow Debugging: ADF Managed Executions Apache Spark Clusters @ ADF Debugging Authoring Data Flow, Graph, Configuration Transformation Setting, Optimize, Inspect Conditional Split Transformation in ADF Pivot Transformation in Mapping Data Flow Pivot Column & Aggregation Functions Pivot Transformation, Pivot Settings Pivot Key Value, Enabling Null Values Pivoted Columns, Pattern, Optimize Column Prefix, Help Graphic, Metadata
Ch 3: Azure Data Factory Architecture ADF Pipeline Design, Publish, Trigger ADF Architecture, Pipelines & ETL DIU : Data Integration Units; Concurrency Linked Service, Dataset & Activities Staging Data - Advantages and Pricing Polybase Indexes, Compression Options Mapping Data Flow, Wrangling Data Flow Pipeline Creation using Copy Data Tool Azure BLOB Storage to Synapse DB Linked Service and Datasets. Mapping Polybase; Staging, Bulk Import Options Validate, Publish Pipelines to ADF Store Pipeline Execution (Triggers), Monitoring Auto Resolving Integration Runtime (IR)	Ch 6: Incremental Loads with ADF - 1 ADF Pipelines with Stored Procedures Watermark Tables and Timestamp Columns Incremental Data Loads to Azure DW New Rows and Old Rows Indentifications Storing High Water Mark Data Stored Procedures for Timestamp Updates Azure Storage Container Incremental Loads Lookup in ADF Portal & ModifiedDate Expressions in ADF Portal for Lookup Expressions in ADF Portal for Source @activity with output Data Pipelines SQL Queries for Dataset Creation Concat Function, Run IDs For File Names ADF Pipeline Validation and Triggers	Ch 9: Wrangling Data Flow in ADF Wrangling Data Flow in ADF : Advantages Power Query Online Editor for Mashup Spark Code for Cloud Scale Executions Wrangling Data For Less Formal Analytics Sources and Sinks with Wrangling DF Github Integration with ADF Repository User Defined Data Stores in GitHub Transformations in Data Wrangling Group By, Aggregate, Reordering Pivot, Aggregations in Power Query ADF Data Types & ADF Pipeline Store Heterogenous Sources in Power Query ADF Publish, GitHub Store Differences
Ch 10: ADF : End to End Implementation Azure Data Share: Configuration & Use; Azure Data Share: PaaS for ADF Shares; Importing BACPAC Files into Azure; Azure SQL DB: Data Lake Storage Gen 2; Data Filters, Aggregations, Joins in ADF; Spark Clusters for DataFlow Debugging; Multi Leve Data Flows in ADF Pipeline Data Loads to Azure Synapse from ADLS; Data Load Settings and Optimization; ADF Pipeline Debugging, Publish in ADF; Data Shares with Azure Synapse Tables; Data Ingestion, Consumption with Synapse; Recipients and Azure AD Users, Accounts; Run IDs, Monitoring, Cost Analysis, Metrics

Module 1

Module 2

Module 3

Ch 1: Azure Data Factory, Synapse Intro

Azure Data Factory (ADF) Operations
Hybrid Data Ingestion, Orchestration
Data Processing & Movement in ADF
Data Pipelines, Flows & Wrangling
Data Mashup and ETL in Azure
Azure Synapse (Data Warehouse)
Enterprise Warehouse with Synapse

Azure Synapse (SQL Pools) Creation
DWUs: Data Warehouse Units Usage
Big Data Storage and Data Analytics
Column Store Data in Azure Synapse
Automated Tuning and Security
Access, Pause/Resume with Synapse
SSMS & ADS Tools Connections

Ch 4: Azure Data Lake with ADF

Creating Azure Data Lake Storage
Data Lake Gen 2 Hierarchial Namespace
Excel Upload to Container, Data Preview
Pipeline Parameters, Variables, OUT
Copy Data Tool: Timeout and Schedule
Secured Pipelines and Linked Services
Sink Options; Colum Mapping, Triggers

Azure SQL Database Loads to Synapse
Azure SQL Database Tables Data Loads
For Each Loops with ADF Pipelines
Copy Data Tool, Pipeline Edits in ADF
Task Schedules and Tumbling Window
Pipeline Execution & Runs; Monitor

Ch 7: Incremental Loads with ADF - 2

Incremental Load Pipeline Design in ADF
Working with Azure Storage Containers
Pipeline Executions, Incremental Schedules
Regular Schedules & Tumbiling Windows
Binay Copy, Last Modified Date in Blob
Pipeline Trigger Schedules, Modifications
Incompatable Rows Skips, Fault Tolerance

Incremental Loads with Mutliple Tables
Stored Procedures, Loops in ADF Pipelines
Configure ETL Sources, Pre-Copy Scripts
Using @{item() with Dyanamic Connections
Table_Schema for Column Mapping
Writing Expressions For Dynamic Loads
Staging and Performance for ADF Loads

Ch 2: Azure Synapse Architecture

MPP - Massively Parallel Processsing
Control Node and Compute Nodes
Azure Storage, DMS and DWUs
Round Robin, Replicate, Hash Tables
Service Level Objective, Sharding
Resource Classes; Gen 1 and Gen 2;
Table Creation, Storage, Distribution

CTAS: Create Table As Select. Indexes
Distribution Types, Time Partitions
Logins and Users in SQL Server
Users and Roles in Synapse SQL DW
Resource Classes; Blob Data Import
COPY INTO Statement in T-SQL
Data Monitoring Scripts with T-SQL

Ch 5: On-Premise Data to Azure

On-Premise Data Sources with Azure
Install Self Hosted Integration Runtime
Access Keys & Use. Configuration Tools
Remote Linked Services in ADF & SH IR
Authentication with Integration Runtime
Sourc, Sink Linked Service Connections
Incompatable Rows Skip, Fault Tolerance

Table Mapping, Column Mapping, Errors
Synapse Pool Connection with Onpremise
Staged Data Copy & ETL Performance
Azure Blob for Staging. Polybase
Connections Management - Preview
Pipeline Exectution, Run IDs, Errors

Ch 8: Mapping Data Flow in ADF

Data Flow Task Creation in ADF Pipelines
Transformation Editor and Parameters
Comparing ADF Pipelines and Data Flow
Debugging: ADF Managed Executions
Apache Spark Clusters @ ADF Debugging
Authoring Data Flow, Graph, Configuration
Transformation Setting, Optimize, Inspect

Conditional Split Transformation in ADF
Pivot Transformation in Mapping Data Flow
Pivot Column & Aggregation Functions
Pivot Transformation, Pivot Settings
Pivot Key Value, Enabling Null Values
Pivoted Columns, Pattern, Optimize
Column Prefix, Help Graphic, Metadata

Ch 3: Azure Data Factory Architecture

ADF Pipeline Design, Publish, Trigger
ADF Architecture, Pipelines & ETL
DIU : Data Integration Units; Concurrency
Linked Service, Dataset & Activities
Staging Data - Advantages and Pricing
Polybase Indexes, Compression Options
Mapping Data Flow, Wrangling Data Flow

Pipeline Creation using Copy Data Tool
Azure BLOB Storage to Synapse DB
Linked Service and Datasets. Mapping
Polybase; Staging, Bulk Import Options
Validate, Publish Pipelines to ADF Store
Pipeline Execution (Triggers), Monitoring
Auto Resolving Integration Runtime (IR)

Ch 6: Incremental Loads with ADF - 1

ADF Pipelines with Stored Procedures
Watermark Tables and Timestamp Columns
Incremental Data Loads to Azure DW
New Rows and Old Rows Indentifications
Storing High Water Mark Data
Stored Procedures for Timestamp Updates
Azure Storage Container Incremental Loads

Lookup in ADF Portal & ModifiedDate
Expressions in ADF Portal for Lookup
Expressions in ADF Portal for Source
@activity with output Data Pipelines
SQL Queries for Dataset Creation
Concat Function, Run IDs For File Names
ADF Pipeline Validation and Triggers

Ch 9: Wrangling Data Flow in ADF

Wrangling Data Flow in ADF : Advantages
Power Query Online Editor for Mashup
Spark Code for Cloud Scale Executions
Wrangling Data For Less Formal Analytics
Sources and Sinks with Wrangling DF
Github Integration with ADF Repository
User Defined Data Stores in GitHub

Transformations in Data Wrangling
Group By, Aggregate, Reordering
Pivot, Aggregations in Power Query
ADF Data Types & ADF Pipeline Store
Heterogenous Sources in Power Query
ADF Publish, GitHub Store Differences

Ch 10: ADF : End to End Implementation

Azure Data Share: Configuration & Use; Azure Data Share: PaaS for ADF Shares; Importing BACPAC Files into Azure; Azure SQL DB: Data Lake Storage Gen 2; Data Filters, Aggregations, Joins in ADF; Spark Clusters for DataFlow Debugging; Multi Leve Data Flows in ADF Pipeline

Data Loads to Azure Synapse from ADLS; Data Load Settings and Optimization; ADF Pipeline Debugging, Publish in ADF; Data Shares with Azure Synapse Tables; Data Ingestion, Consumption with Synapse; Recipients and Azure AD Users, Accounts; Run IDs, Monitoring, Cost Analysis, Metrics

Module 3: SSIS Training Content

Module I: Basic SSIS Development & ETL - DWH	Module II: Advanced SSIS Development & Deployments
Ch 1: SSIS INTRODUCTION & INSTALLATION Need for SQL Server Integration Services (SSIS) & ETL / DWH Advantages of SSIS for Data Loads, Operations, ETL, Warehouse SSIS Tools : SSDT (SQL Server Data Tools), ETL Wizards SSIS Design / Development and LIVE (Deployment) Environment Understanding Data Warehouse (DWH) Design and ETL Process DWH and ETL Structures, Implementations with MSBI SSIS SSIS ETL Operations for Data Reads, Data Cleansing, DWH Data Warehouse (DWH) Design Principles and Design Plan SSIS 2019 and SSIS 2017 : SSIS Database Installations SSIS Database & Catalog Folders - Purpose, Usage, Passwords SSIS Catalog Database (SSIS DB) : Creation, Verification SSDT Tool: SQL Server Data Tools - SSDT / Visual Studio Shell SSDT Installation and Verification of SSIS Templates Basic Vocabulary - SSIS, ETL, DWH, Data Flow, Data Buffer SSIS Package Environment and SSDT Project Creation SSIS Solutions and Project Templates - Verifications Version Control Options and SSIS Design Plan - Tools Usage SSIS & ETL Training - Lab Plan, Resources & Work Flow	Ch 7: CHECKSUM TRANSFORMATION, DWH DESIGN Configuring Checksum Transformation : 2019, 2017 Versions Checksum Transformation Logic and Parity Checks (CRC) Codes Working with CHECKSUM Transformation & Parity Bit Columns Generating Checksum Values For Type I, Type II ETL Changes DWH Dimension Tables With Checksum Transformation Working with Parity Bits, Conditional Lookup Transformation Row Redirection Options & Lookup Match, NonMatch Output OLE DB Command: SQL Statements and Input Parameters Parameter Mapping Options and Dynamic SQL for Row Updates Cache Transformation with Memory Loads, CAW Files (VM) Memory Connections and Reuse. Lookup with Cache Options Tuning Lookup Transformation: Caching Options, Index Options FULL CACHE & PARTIAL CACHE. Cache Connection Manager Pre-ETL Data Load Operations with Memory Connections Lookup with NOCACHE Options. Dependent Data Flow Tasks Post ETL, Derived Column and Fuzzy Transformations SSIS Package Internal Parameters and Query Updates OLEDB Command with Conditional Splits, Multi Row Updates
Ch 2: SSIS ETL PACKAGES: EXTRACT,TRANSFORM,LOAD Control Flow Tasks - Architecture, Purpose and Usage Data Flow Tasks - Architecture, Purpose and Usage Creating SSIS Packages For Basic Data Flow Operations SSIS Solutions, Projects and Package Creation. Tasks Need For Data Pipelines & Connections in Data Flow Tasks Understanding SSIS Package (Program) Execution Process Working with Data Flow Objects and Tasks in SSIS Using OLE DB and SQL Server Connections - Usage SSIS Package Creation Process - Using Control Flow Items Using DTSX Files for SSIS Package Execution. Audits SSIS Execution Context, SSIS Package Errors and Logs Data Flow Transformation: Conditional Split, Expressions Excel Connections, Sheets, & Memory References - Usage Using Source Assistants, Destination Assistants @ SSIS DAT File Imports, Data Flow Options, Adding Annotations SSIS Project Configuration Options - Debugging, Bit Config SSIS 64 Bit and 32 Bit Configuration Settings and Options	Ch 8: CHANGE DATA CAPTURE: DWH DIMENSIONS Understanding CDC Tables with SQL Server Connections DML Audits: Inserts, Deletes, Values before/after Updates CDC Source Connections - Usage, ADO.NET Integration CDC Control Flow Tasks and CDC State Tasks. Values INITIAL LOAD START / END. PRCESSING RANGE Using State Variables and Controls. Storing States SSIS CDC Source Connections with Net Support Changes SSIS CDC Control Tasks and Caching, State Variables Understanding State Variables and SSIS Logging Tables Initial and Incremental Dimension Loads with CDC Dynamic CDC Connections, Parameter Mapping Integrating Control Flow and Data Flow for CDC @ ETL CDC Splitter Transformation - Row Inserts and Updates CDC Splitter Precautions, Input Types and Output Range Master Packages and Child Packages, Derived Columns Defining Local and Global Variables/Parameters Parameter Bindings with Parent - Child Packages
Ch 3: MERGE, FUZZY LOOKUP, DATA CONVERSION MERGE Transformation and UNION ALL Transformation SORT Transformation, NOSORT Options, Advanced Sort Synchronous & Asynchronous Transformations, Data Loads Row and Partial Blocking Transformations. Buffer Reuse Fully Blocking Transformations - Buffer Reuse Restrictions Examples to Avoid Fully Blocking Transformation in SSIS Bulk Load Operations and Data Import Options in SSIS IsSorted and SortKey Positions with Advanced Options Measuring SSIS Package Performance & Resource Usage Data Conversion Transformation and SSIS Expressions Fuzzy Lookup Transformation, Reference Table Connections Exact, Fuzzy & Nomatch Data Cleansing with Conditional Split Index Creation, Maintenance for Faster Lookup Transformation Data Conversion Transformation - Usage. Derived Columns Data Conversion Transformation: NVARCHAR, VARCHAR Data Threshold Values Selection, Search Delimiters and Options Data Pipeline with _Similarity, _Confidence Columns. Usage Explicit Data Type Conversions and Usage. Error Redirections	Ch 9: FACT TABLE DESIGN, FACT LOADS (DWH) Fact Table - Design and Implementation Rules, ER Model Fact Table Design with STAR and SNOWFLAKE Schemas Time Dimensions - Purpose, Usage and ETL Date / Time Generating Time Dimensions for ETL, Load Identification Parent-Child Packages for Dimensional Loads, Control Flow Parent-Child Packages for Dimensions & Fact Table Loads Master Package Design: Dimension, Fact Load Integration Inferred Members and Status for NULLs in Dimensions Parameter Mapping @ ETL Updates, Component Properties ETL Load Date IDs & Expressions for Dimension Keys Error Handling & Event Handling with Master Packages Text Qualifiers and Parameters in Master-Child Packages Key Based Dimension LOOKUPs and Indexes. Fast Loads SCD Implementations with Legacy Data. ETL Load Types Fact Load Design for Initial Loads, Incremental Updates End-to-End Data Warehouse Design: Implementation Direct Data Loads and Data Stage Operations for DWH DBs STAR & SNOWFLAKE Schemas for DWH - Data Modelling
Ch 4: PACKAGE PROPERTIES, CHECKPOINT, PIVOT Execute SQL Task and OLE DB Queries - Connection Options Transaction Options For SSIS Executables - Package Level Precedence Constraints - Success / Failure / Completion SSIS Package Level Rollbacks and Restart Execution Options SSIS Checkpoints - Purpose and Usage with Data Flow Tasks Checkpoint Files and SSIS Logging Options - Package, Tasks Transactions with Checkpoint File Usage in SSIS Packages Checkpoint Options - Advantages, Usage and Limitations FailPackageOnFailure Options and Checkpoint Property Usage Transaction Property : REQUIRED / SUPPORT, NOTSUPPORTED Transaction Property Versus CHECKPOINT Files, Usage Importing Legacy Data, Need for Data Cleansing, Formatting Understanding Denormalization and Keys. Need for OLTP PIVOT Transformation, Data Reads, Connection Assistant Pivot Usage Values - Purpose and Implementation. Key Values Lineage ID in SSIS - Purpose, Usage Options. Data Mappings Lineage IDs for Column Mapping and Pivot Keys SSIS Input Columns, Mappings and Source Column Values Using Data Viewer (Debugging) for Data Transfer Verifications Explicit Data Type Conversions, Usage. Error Redirections	Ch 10: DWH MIGRATIONS with SSIS, SCRIPT Task DataWarehouse Migrations with SSIS Control Flow Using SSIS Containers and Db Integrity Task Using Pre-Database Migration Tasks, Precautions Transfer Database Task and DB File Options Online and Offline DB Transfer Options, Usage Copy / Move Options with DWH Migrations SMTP : Simple Mail Transfer Protocol For Emails Using SQL Server Agent Operators, Package Events Precedence Constraint with Containers, SSIS Tasks Using Data Profiling Task with ADO.NET Connectors XML Files Generation, Using SSIS Data Profiler Tool Script Task - Purpose and Working in SSIS Control Flow Script Task - Usage with VB.NET Programs. Compilations Variables and Parameters with SSIS Script Task. Usage Read Only and Read Write Variables. Expressions, Mapping Data Flow Limitations : Solutions with .NET Scripting SSIS Expressions and Package Debugging, Break Points SSIS Variables and Parameters - Mapping Expressions Using Control Flow File System Tasks and Limitations SQLDataAdapters & System.Data.SQLClient Connections
Ch 5: EVENTS, LOOPS, EXPRESSIONS SSIS Package Events : Validation Events, Execution Events PreExecution, Progress and Cleanup Events in SSIS Auditing SSIS Events, Errors / Warnings / Information Configuring and Using sysssislog System Tables Package Debugging : Data Viewers and Breakpoints ForEach Loop Container Usage. File Connections, Variables Defining Variables for Connections. DFT, Control Flow Links Dynamic Connections with Variables. Connection Strings Connection Iterations, Connection Fetch, Index Mapping SSIS Expressions and Usage for ETL and DWH FOR LOOP Expressions in SSIS, Examples for ETL InitExpression, EvalExpression, AssignExpression SSIS Expression Builder, Functions, Evaluated Values Data Insertions and Data Serializations with SSIS Counter Values, Variables and Parameter Mapping Using SSIS for OS Level Drive Operations, Loops Using Execute SQL Task : Variables, Return Values	Ch 11: SSIS PROJECT DEPLOYMENT, UPGRADES SSISDB Catalog Deployments - Process & ISPAC Files SSIS Package Builds, Verification Techniques, Scripts SSIS Project Deployment Wizard : Targets and Logging SSIS DB Catalog Folders - Creation, Usage. Properties SSIS DB Catalog Projects - Creation, Usage. Properties SSIS Package Executions - Scripts, Reports, Messages Package Validations - Scripts, Reports, 32/64 bit Options Package Configurations - Parameter Management, Scripts SSIS Package Jobs @ SQL Server Agent. SSIS Job Steps SSIS Job Schedules and Notifications: Windows Event Logs Package Security Management - SSISDB Logins and Users Folder Level and Project Level Security Implementation Project Migration Utilities and Package Upgrades SSIS Package Imports and Exports with ISPAC Files Command-Line Deployments For SSIS, Execution Utilities SSIS Package Execution Reports; Validation Reports SSIS Package Versions and Restores, SSIS Rollbacks
Ch 6: DATAWAREHOUSE (DWH) DESIGN WITH SSIS Introduction to Datawarehouse (DWH) - Purpose, Usage, Access Technical Terms: Dimensions, Attributes, Members and Types Dimension Tables, Fact Tables and Relations, Inferred Members TYPE1 and TYPE2 Changes. ETL Implementation Techniques Implementing Type I Changes (SCD) for DWH : Sales Scenario Dimensional Table Design for DWH using SCD - Type 1, Type 2 Initial Data Loads with Legacy Files (DAT, TXT, CSV), Data Marts Working with Business Keys, non Identity Key Based Columns Surrogate Keys and Alternate Business Keys - Purpose, Usage Identity Columns, Key Values in DWH. Business Key Limitations Cascading Source OLTP / Stage Changes to DWH Inactive Rows Changes to Fixed Attributes, Changing Attributes. Observations Changes to Historical Attributes. Inferred Member Updated ETL Date Fields, Row Status Values and Transformations Designing Dimensions for DW - Incremental, Historical Loads Historical Attributes, Data Delta Operations, Identification SSIS SCD Transformation Limitations and Real-time Issues	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 INTRODUCTION & INSTALLATION

Need for SQL Server Integration Services (SSIS) & ETL / DWH
Advantages of SSIS for Data Loads, Operations, ETL, Warehouse
SSIS Tools : SSDT (SQL Server Data Tools), ETL Wizards
SSIS Design / Development and LIVE (Deployment) Environment
Understanding Data Warehouse (DWH) Design and ETL Process
DWH and ETL Structures, Implementations with MSBI SSIS
SSIS ETL Operations for Data Reads, Data Cleansing, DWH
Data Warehouse (DWH) Design Principles and Design Plan
SSIS 2019 and SSIS 2017 : SSIS Database Installations

SSIS Database & Catalog Folders - Purpose, Usage, Passwords
SSIS Catalog Database (SSIS DB) : Creation, Verification
SSDT Tool: SQL Server Data Tools - SSDT / Visual Studio Shell
SSDT Installation and Verification of SSIS Templates
Basic Vocabulary - SSIS, ETL, DWH, Data Flow, Data Buffer
SSIS Package Environment and SSDT Project Creation
SSIS Solutions and Project Templates - Verifications
Version Control Options and SSIS Design Plan - Tools Usage
SSIS & ETL Training - Lab Plan, Resources & Work Flow

Ch 7: CHECKSUM TRANSFORMATION, DWH DESIGN

Configuring Checksum Transformation : 2019, 2017 Versions
Checksum Transformation Logic and Parity Checks (CRC) Codes
Working with CHECKSUM Transformation & Parity Bit Columns
Generating Checksum Values For Type I, Type II ETL Changes
DWH Dimension Tables With Checksum Transformation
Working with Parity Bits, Conditional Lookup Transformation
Row Redirection Options & Lookup Match, NonMatch Output
OLE DB Command: SQL Statements and Input Parameters
Parameter Mapping Options and Dynamic SQL for Row Updates

Cache Transformation with Memory Loads, CAW Files (VM)
Memory Connections and Reuse. Lookup with Cache Options
Tuning Lookup Transformation: Caching Options, Index Options
FULL CACHE & PARTIAL CACHE. Cache Connection Manager
Pre-ETL Data Load Operations with Memory Connections
Lookup with NOCACHE Options. Dependent Data Flow Tasks
Post ETL, Derived Column and Fuzzy Transformations
SSIS Package Internal Parameters and Query Updates
OLEDB Command with Conditional Splits, Multi Row Updates

Ch 2: SSIS ETL PACKAGES: EXTRACT,TRANSFORM,LOAD

Control Flow Tasks - Architecture, Purpose and Usage
Data Flow Tasks - Architecture, Purpose and Usage
Creating SSIS Packages For Basic Data Flow Operations
SSIS Solutions, Projects and Package Creation. Tasks
Need For Data Pipelines & Connections in Data Flow Tasks
Understanding SSIS Package (Program) Execution Process
Working with Data Flow Objects and Tasks in SSIS
Using OLE DB and SQL Server Connections - Usage
SSIS Package Creation Process - Using Control Flow Items

Using DTSX Files for SSIS Package Execution. Audits
SSIS Execution Context, SSIS Package Errors and Logs
Data Flow Transformation: Conditional Split, Expressions
Excel Connections, Sheets, & Memory References - Usage
Using Source Assistants, Destination Assistants @ SSIS
DAT File Imports, Data Flow Options, Adding Annotations
SSIS Project Configuration Options - Debugging, Bit Config
SSIS 64 Bit and 32 Bit Configuration Settings and Options

Ch 8: CHANGE DATA CAPTURE: DWH DIMENSIONS

Understanding CDC Tables with SQL Server Connections
DML Audits: Inserts, Deletes, Values before/after Updates
CDC Source Connections - Usage, ADO.NET Integration
CDC Control Flow Tasks and CDC State Tasks. Values
INITIAL LOAD START / END. PRCESSING RANGE
Using State Variables and Controls. Storing States
SSIS CDC Source Connections with Net Support Changes
SSIS CDC Control Tasks and Caching, State Variables
Understanding State Variables and SSIS Logging Tables

Initial and Incremental Dimension Loads with CDC
Dynamic CDC Connections, Parameter Mapping
Integrating Control Flow and Data Flow for CDC @ ETL
CDC Splitter Transformation - Row Inserts and Updates
CDC Splitter Precautions, Input Types and Output Range
Master Packages and Child Packages, Derived Columns
Defining Local and Global Variables/Parameters
Parameter Bindings with Parent - Child Packages

Ch 3: MERGE, FUZZY LOOKUP, DATA CONVERSION

MERGE Transformation and UNION ALL Transformation
SORT Transformation, NOSORT Options, Advanced Sort
Synchronous & Asynchronous Transformations, Data Loads
Row and Partial Blocking Transformations. Buffer Reuse
Fully Blocking Transformations - Buffer Reuse Restrictions
Examples to Avoid Fully Blocking Transformation in SSIS
Bulk Load Operations and Data Import Options in SSIS
IsSorted and SortKey Positions with Advanced Options
Measuring SSIS Package Performance & Resource Usage

Data Conversion Transformation and SSIS Expressions
Fuzzy Lookup Transformation, Reference Table Connections
Exact, Fuzzy & Nomatch Data Cleansing with Conditional Split
Index Creation, Maintenance for Faster Lookup Transformation
Data Conversion Transformation - Usage. Derived Columns
Data Conversion Transformation: NVARCHAR, VARCHAR Data
Threshold Values Selection, Search Delimiters and Options
Data Pipeline with _Similarity, _Confidence Columns. Usage
Explicit Data Type Conversions and Usage. Error Redirections

Ch 9: FACT TABLE DESIGN, FACT LOADS (DWH)

Fact Table - Design and Implementation Rules, ER Model
Fact Table Design with STAR and SNOWFLAKE Schemas
Time Dimensions - Purpose, Usage and ETL Date / Time
Generating Time Dimensions for ETL, Load Identification
Parent-Child Packages for Dimensional Loads, Control Flow
Parent-Child Packages for Dimensions & Fact Table Loads
Master Package Design: Dimension, Fact Load Integration
Inferred Members and Status for NULLs in Dimensions
Parameter Mapping @ ETL Updates, Component Properties

ETL Load Date IDs & Expressions for Dimension Keys
Error Handling & Event Handling with Master Packages
Text Qualifiers and Parameters in Master-Child Packages
Key Based Dimension LOOKUPs and Indexes. Fast Loads
SCD Implementations with Legacy Data. ETL Load Types
Fact Load Design for Initial Loads, Incremental Updates
End-to-End Data Warehouse Design: Implementation
Direct Data Loads and Data Stage Operations for DWH DBs
STAR & SNOWFLAKE Schemas for DWH - Data Modelling

Ch 4: PACKAGE PROPERTIES, CHECKPOINT, PIVOT

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

Importing Legacy Data, Need for Data Cleansing, Formatting
Understanding Denormalization and Keys. Need for OLTP
PIVOT Transformation, Data Reads, Connection Assistant
Pivot Usage Values - Purpose and Implementation. Key Values
Lineage ID in SSIS - Purpose, Usage Options. Data Mappings
Lineage IDs for Column Mapping and Pivot Keys
SSIS Input Columns, Mappings and Source Column Values
Using Data Viewer (Debugging) for Data Transfer Verifications
Explicit Data Type Conversions, Usage. Error Redirections

Ch 10: DWH MIGRATIONS with SSIS, SCRIPT Task

DataWarehouse Migrations with SSIS Control Flow
Using SSIS Containers and Db Integrity Task
Using Pre-Database Migration Tasks, Precautions
Transfer Database Task and DB File Options
Online and Offline DB Transfer Options, Usage
Copy / Move Options with DWH Migrations
SMTP : Simple Mail Transfer Protocol For Emails
Using SQL Server Agent Operators, Package Events
Precedence Constraint with Containers, SSIS Tasks
Using Data Profiling Task with ADO.NET Connectors
XML Files Generation, Using SSIS Data Profiler Tool

Script Task - Purpose and Working in SSIS Control Flow
Script Task - Usage with VB.NET Programs. Compilations
Variables and Parameters with SSIS Script Task. Usage
Read Only and Read Write Variables. Expressions, Mapping
Data Flow Limitations : Solutions with .NET Scripting
SSIS Expressions and Package Debugging, Break Points
SSIS Variables and Parameters - Mapping Expressions
Using Control Flow File System Tasks and Limitations
SQLDataAdapters & System.Data.SQLClient Connections

Ch 5: EVENTS, LOOPS, EXPRESSIONS

SSIS Package Events : Validation Events, Execution Events
PreExecution, Progress and Cleanup Events in SSIS
Auditing SSIS Events, Errors / Warnings / Information
Configuring and Using sysssislog System Tables
Package Debugging : Data Viewers and Breakpoints
ForEach Loop Container Usage. File Connections, Variables
Defining Variables for Connections. DFT, Control Flow Links
Dynamic Connections with Variables. Connection Strings
Connection Iterations, Connection Fetch, Index Mapping

SSIS Expressions and Usage for ETL and DWH
FOR LOOP Expressions in SSIS, Examples for ETL
InitExpression, EvalExpression, AssignExpression
SSIS Expression Builder, Functions, Evaluated Values
Data Insertions and Data Serializations with SSIS
Counter Values, Variables and Parameter Mapping
Using SSIS for OS Level Drive Operations, Loops
Using Execute SQL Task : Variables, Return Values

Ch 11: SSIS PROJECT DEPLOYMENT, UPGRADES

SSISDB Catalog Deployments - Process & ISPAC Files
SSIS Package Builds, Verification Techniques, Scripts
SSIS Project Deployment Wizard : Targets and Logging
SSIS DB Catalog Folders - Creation, Usage. Properties
SSIS DB Catalog Projects - Creation, Usage. Properties
SSIS Package Executions - Scripts, Reports, Messages
Package Validations - Scripts, Reports, 32/64 bit Options
Package Configurations - Parameter Management, Scripts
SSIS Package Jobs @ SQL Server Agent. SSIS Job Steps

SSIS Job Schedules and Notifications: Windows Event Logs
Package Security Management - SSISDB Logins and Users
Folder Level and Project Level Security Implementation
Project Migration Utilities and Package Upgrades
SSIS Package Imports and Exports with ISPAC Files
Command-Line Deployments For SSIS, Execution Utilities
SSIS Package Execution Reports; Validation Reports
SSIS Package Versions and Restores, SSIS Rollbacks

Ch 6: DATAWAREHOUSE (DWH) DESIGN WITH SSIS

Introduction to Datawarehouse (DWH) - Purpose, Usage, Access
Technical Terms: Dimensions, Attributes, Members and Types
Dimension Tables, Fact Tables and Relations, Inferred Members
TYPE1 and TYPE2 Changes. ETL Implementation Techniques
Implementing Type I Changes (SCD) for DWH : Sales Scenario
Dimensional Table Design for DWH using SCD - Type 1, Type 2
Initial Data Loads with Legacy Files (DAT, TXT, CSV), Data Marts
Working with Business Keys, non Identity Key Based Columns

Surrogate Keys and Alternate Business Keys - Purpose, Usage
Identity Columns, Key Values in DWH. Business Key Limitations
Cascading Source OLTP / Stage Changes to DWH Inactive Rows
Changes to Fixed Attributes, Changing Attributes. Observations
Changes to Historical Attributes. Inferred Member Updated
ETL Date Fields, Row Status Values and Transformations
Designing Dimensions for DW - Incremental, Historical Loads
Historical Attributes, Data Delta Operations, Identification
SSIS SCD Transformation Limitations and Real-time Issues

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 4: SQL Server & T-SQL Basics Training Content

Ch 1: SQL SERVER INTRODUCTION

Data, Databases and RDBMS Software
Database Types : OLTP, DWH, OLAP
Microsoft SQL Server Advantages, Use
Versions and Editions of SQL Server
SQL : Purpose, Real-time Usage Options
SQL Versus Microsoft T-SQL [MSSQL]
Microsoft SQL Server - Career Options

SQL Server Components and Usage
Database Engine Component and OLTP
BI Components, Data Science Components
ETL, MSBI and Power BI Components
Course Plan, Concepts, Resume, Project
24 x 7 Online Lab for Remote DB Access
Software Installation Pre-Requisites

Ch 5: SQL Basics - 3, T-SQL 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 Retreival and 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: JOINS, T-SQL QUERIES Level 3

GetDate, Year, Month, Day Functions
Date & Time Styles, Data Formatting
DateAdd and DateDiff Functions
Cast and, Convert Functions in Queries
String Functions: SubString, Relicate
Len, Upper, Lower, Left and Right
LTrim, RTrim, CharIndex Functions

MERGE Statement - Comparing Tables
WHEN MATCHED and NOT MATCHED
Incremental Load with MERGE Statement
IIF() Function for Value Compares
CASE Statement : WHEN, ELSE, END
ROW_NUMBER() and RANK() Queries
Dense Rank and Partition By Queries

Ch 2: SQL SERVER INSTALLATIONS

System Configuration Checker Tool
Versions and Editions of SQL Server
SQL Server and SSMS Installation Plan
SQL Server Pre-requisites : S/W, H/W
SQL Server 2016 / 2017 Installation
SQL Server 2019 Installation
Instance Name and Server Features
Instances : Types and Properties

Default Instance, Named Instances
Port Numbers, Instance Differences
Service and Service Account Use
Authentication Modes and Logins
Windows Logins and SQL Logins
FileStream and Collation Properties

Ch 6 : CONSTRAINTS,INDEXES 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 and 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: VIEW, SPs, Function Basics

Views : Types, Usage in Real-time
System Predefined Views and Audits
Listing Databases, Tables, Schemas
Functions : Types, Usage in Real-time
Scalar, Inline and Multi-Line Functions
System Predefined Functions, Audits
DBId, DBName, ObjectID, ObjectName
Variables & Parameters in SQL Server

Procedures : Types, Usage in Real-time
User & System Predefined Procedures
Parameters and Dynamic SQL Queries
Sp_help, Sp_helpdb and sp_helptext
sp_pkeys, sp_rename and sp_help
Important System Objects and Metadata

Ch 3: SSMS Tool, SQL BASICS - 1

SQL Server Management Studio
Local and Remote Connections
System Databases: Master and Model
MSDB, TempDB, Resource Databases
Creating Databases : Files [MDF, LDF]
Creating Tables in User Interface
Data Insertion & Storage. Limitations

SQL : Purpose and Real-time Usage
SQL Versus T-SQL : Basic Differences
DDL, DML, SELECT, DCL and TCL
Creating Tables using SQL Scripts
Data Storage, Inserts - Basic Level
Table Data Verifications with Select
SELECT Statement for Table Retrieval

Ch 7: JOINS, T-SQL Queries : Level 1

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

Join Unrelated Tables in SQL Server
NULL, IS NULL Operators in Joins
CROSS JOIN and CROSS APPLY
CROSS JOIN Versus CROSS APPLY
One-way & Two Way Data Comparisons
Important Join Queries in T-SQL
Join Options: Merge, Loop, Hash

Ch 11: 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
Open Transaction Scenarios & Cause
Query Blocking Scenarios @ Real-time
NOLOCK and READPAST Lock Hints

Ch 4: SQL BASICS - 2

Creating Databases & Tables in SSMS
Single Row Inserts, Multi Row Inserts
Rules for Data Insertion Statements
SELECT Statement @ Data Retrieval
SELECT with WHERE Conditions
Batch Concept and Go Statement
AND and OR Operators Usage
IN Operator and NOT IN Operator

Between, Not Between Operators
LIKE and NOT LIKE Operators
UPDATE Statement & Conditions
DELETE & TRUNCATE Statements
Logged and Non-Logged Operations
ADD, ALTER and DROP Columns
ALTER & DROP Table Statements

Ch 8: Group By, T-SQL Queries Level 2

GROUP BY Queries and Aggregations
Group By Queries with Having Clause
Group By Queries with Where Clause
Using WHERE and HAVING in T-SQL
Rollup : Usage and T-SQL Queries
Cube : Usage and T-SQL Queries
UNION and UNION ALL Operator
EXISTS Operator, Query Conditions

Sub Queries and Alternatives to Joins
Using Joins with Group By Queries
Using Joins with Nested Sub Queries
Sub Queries with Joins and Group By
Using UNION and UNION ALL in Queries
Nested Sub Queries with Group By, Joins
Comparing WHERE, HAVING Conditions

Ch 12 : ER MODELS, NORMAL FORMS

Normal Forms for Entity Relationships
First Normal Form and Atomocity
Second Normal Form, Candidate Keys
3rd Normal Form Multi Value Dependancy
Boycee-Codd Normal Form : BNCF
Fourth Normal Form Realtime Advantages
Self Joins & Self Reference Keys
1:1, 1:M, M:1, M:M Relationship Types

Joins with Group By Queries
Joins with Sub Queries, Formating
Office Data Connections, Excel Reports
Excel Pivot Reports and Reports
SQL Queries (Auto Generated) in BI Tools
FETCH OFFSET, NEXT ROWS, Order By
Data Refresh (Manual and Automated)

Real-time Case Study - 1 (Sales & Retail)
Objective : Database Design, Table Design and Relations.
Involves Purchases, Products, Customers and Time Data with Various Data Types.

Real-time Case Study - 2 (Sales & Retail)
Objective : Queries, Excel Integration
Pivot Tables, Pivot Charts, ODC Connections