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:


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;
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 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]

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

Our Azure SSIS Online Trainings are completely practical, realtime. Register today for free Demo

 

Benefits of Azure SSIS Training Course:

  • Completely Practical and Realtime
  • Theory Material provided in Advance
  • Highly Interactive and Interesting
  • Certification Guidance and FAQs
  • 24x7 Server Access with Realtime DBs
  • Beneficial for DP 200, 201 Exams

After our Azure SSIS Training Course course participants be able to:

  • Understand Azure Fundamentals, Azure Data Factory
  • Table Design with Azure Synapse (SQL Pools)
  • Exhibit ADF Pipeline Design Skills with Parameters
  • On-premise SSIS Package Migrations to Azure
  • Working with Loops, Conditional Split, Data Flows
  • Work with Data Wrangling, MPP Queries, CTAS Queries

Job-Oriented Real-time Training @ SQL School Training Institute