SSIS Training

SSIS Classroom 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 Trainings, we deal with complete SSIS Design and Development including SCD, Profiling, Tuning and Fact Loads. All sessions are completely practical and realtime.

Prerequisites: Participant should have knowledge on SQL Server T-SQL Queries & Tuning to join this Real-time Practical SSIS Training Course.

 
 
Schedules for SSIS (ETL, DWH) Classroom Training

  Timings (IST) Register
   
Schedule 1 8 AM to 9:15 AM Register
Schedule 2 11:30 AM to 1 PM Register
Schedule 3 8 PM to 9:30 PM Register

 

This MSBI Classroom Training (SSIS, SSAS, SSRS) Course includes ONE Real-time Project.     Versions: MSBI 2016, 2014, 2012

 

Trainer: Mr Sai Phanindra T (11+ Yrs Exp)
 
Dur: 3 Weeks (Mon - Fri)

Total Course Fee: INR 5,500/-

 
HIGHLIGHTS
✔ Daily Tasks ✔ Weekly Interviews
✔ Real-time Project ✔ Resume Guidance
✔ SSIS Certifications    ✔ Placement Services

 
 
Module I: Basic SSIS Development & ETL - DWH

7 Sessions (LIVE, Practical - Realtime)
Module II: Advanced SSIS Development & Deployments

7 Sessions (LIVE, Practical - Realtime)

Chapter 1: SSIS INTRODUCTION, INSTALLATION & TOOLS

  • 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
  • Installing SSIS Databases For SQL Server 2016 and 2014
  • Understanding SSIS Catalog DB Encryptions and CLR / Startup
  • SSIS Database and Catalog Folders - Purpose, Usage, Passwords
  • SSIS Configuration Options and Encryption Options
  • SSIS Catalog Database (SSIS DB) : Creation, Verification
  • SSDT Tool: SQL Server Data Tools - SSDT / Visual Studio Shell
  • SSDT 2015 Installation and Verification of SSIS Templates
  • SSDT 2013 Installation and Verification of SSIS Templates
  • Understanding SSIS Developer Environment (SSDT) Interface
  • 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.
  • SSIS Online Training - Lab Plan, Resources & Databases

Chapter 8: CHANGE DATA CAPTURE: DWH - DIMENSION LOADS

  • DML Audits using Change Data Capture (SQL Server)
  • 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, MARKING
  • Using State Variables and Controls. Storing States
  • SSIS CDC Connections with Net Changes, Variables
  • Understanding State Variables and Logging Tables
  • Initial Data Load Packages for Dimension Tables
  • Incremental Data Load Packages for Dimension Tables
  • Dynamic CDC Connections and Data Load Options
  • OLE DB Commands and Data Warehouse (DWH) Updates
  • Working with Internal Parameters and Usage Options
  • Parameter Mapping For Row Updates in ETL Type1, Type2
  • Integrating Control Flow and Data Flow for CDC @ ETL
  • CDC Splitter Transformation - Row Inserts and Updates
  • CDC Splitter Precautions, Input Types and Output Range
  • Derived Column Transformations with CDC Transformation
  • Limitations of ADO.NET Connections and CDC Process
  • Conditional Precedence Constraints for Dimension Tables
  • Custom Code Handling with .NET Script in Data Warehouse

Chapter 2: SSIS PACKAGES: EXTRACT, TRANSFORM, LOAD(ETL)

  • 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 & Tasks in SSIS
  • Basic Data Extraction Drivers / Providers with Data Flow
  • Using OLE DB and SQL Server Connections - Usage
  • SSIS Package Creation Process - Using Control Flow Items
  • SSPI Interface Connections, Data Source, Initial Catalog
  • Connection Management Interface, Execution Interface
  • 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
  • Common SSIS Package Errors & Solutions in Real-time
  • SSIS Project Configuration Options - Debugging, Bit Config
  • SSIS Build and Version Control Options. Naming Conventions
  • SSIS 64 Bit and 32 Bit Configuration Settings and Options
  • Error Outputs and Error Row Redirections in Data Flow Tasks

Chapter 9: MASTER - CHILD PACKAGES & DEPLOYMENTS

  • Parent - Child Package Design in SSIS, Options
  • Master Packages in SSIS - Configuration, Usage
  • Child Packages in SSIS - Configuration, Usage
  • Connection Parameters in SSIS - Data Types, Scope
  • Defining Local and Global Variables/Parameters
  • Local Connections - Package Level. Naming Conventions
  • Global Connections - Project Level. Naming Conventions
  • Package to Project Connection Conversion. Reusability
  • Package Parameters - Scope, Data Types and Defaults
  • Project Parameters - Scope, Data Types and Defaults
  • Parameter Bindings with Parent - Child Packages in SSIS
  • Using Execute Package Control Flow Tasks, Options
  • Execute Out Of Process and SSIS Parameter Mappings
  • Conditional Data Loads (ETL) and Fact Table Design
  • Fact Loads From OLTP, Dimensions with CDC Transformation
  • ETL Techniques for Dimension Loads in Data Warehouse
  • Data Delta, Differential Data Loads with SSIS Expressions
  • SQL Server and ADO Connections with DataAdapters
  • Derived Columns and Data Conversions Transformations
  • SSIS Project Builds and ISPAC File Creation. Deployments
  • Understanding Validation and Verification Techniques
  • SSIS Package Deployments and Execution Utility, Scripts
  • Package Configuration Options, SSIS Catalog Database

Chapter 3: ETL FAST LOADS, MERGE, UNION ALL, CONVERSIONS

  • Understanding ETL and DWH Implementations
  • Kimball Method of BI Design and Keys Concepts
  • Bulk Load Operations and Data Import Options in SSIS
  • Bypass Prepare and Execution Options with SQL Task
  • Historical Data Loads and Incremental Updates
  • Debugging Controls in SSIS - Variants and Limitations
  • Data Flow Debugging Controls and Data Viewer Options
  • 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
  • MERGE Transformation and UNION ALL Transformation
  • SORT Transformation, NOSORT Options, Advanced Sort
  • Data Conversion Transformation, SSIS Expressions
  • Data Flow Tuning with Query Locks and Performance
  • Tuning Data Flow Tasks with Fast Loads Options (OLE DB)
  • Fast Load Options for Data Flow in OLE DB Connections
  • Tabular Data Stream (TDS) Packet Sizing and Tuning
  • SSIS Package Tuning with Row Size Calculations, Batches
  • Rows Per Batch Calculations - Variants, Commit Size Options
  • Transactions and Batch Scoped Data Loads in SSIS
  • Comparing Regular Loads and Fast loads in ETL / DW

Chapter 10: XML CONNECTIONS, ODBC & DYNAMIC LOOPS

  • System.Data.SQLClient Managed Providers: Usage Options
  • Dynamic Connections and Loops with SSIS Expressions
  • SSIS Expressions with Variables For File Specifications
  • For Each Expressions - Dynamic File Types, Directories
  • Variable Mapping with Indexed Connections. Performance
  • XML Connections & Usage. Reading, Operating XML Data
  • OLE DB Connections with SQL Native Client (SNAC)
  • ODS Connection Files, Reusability Options (Dynamic)
  • Advanced Connection Options - XML, DT_NTEXT Stream
  • XML Queries, Options @ Sub Queries. XML Data Imports
  • RAW Files - DTSPipeline Wrapper & RAW Adapters
  • Row Sampling and Percentage Sampling Options
  • SSIS Logging - Text Files and sysssislog Tables @ Audits
  • Package Logging and Container Events for Execution
  • SSIS Logging Options and Connections. Verifications
  • SSIS Data Profiler Tool, ADO.NET Connections and XML
  • SQL Data Profile Viewer Tool - Usage with XML Files
  • Nullability Checks and Fast Load Options @ Row Size
  • ADO.NET Connections for SQL Profiler Tool, Options
  • Time-Out Options for Quick Profile, Candidate Keys
  • Value and Length Distribution, Surrogate Key Detection
  • XML File Imports for SQL Profiler Viewer Tool, Options

Chapter 4: PIVOT, FUZZY LOOKUP, DATA CLEANSING, LOOPS

  • Importing Legacy Data, Need for Data Cleansing, Formatting
  • Understanding Denormalization and Keys Concepts. Need for OLTP
  • PIVOT Transformation Usage, Data Reads, Connection Assistant
  • Pivot Usage Values - Purpose and Implementation. Key Values
  • Lineage ID in SSIS - Purpose, Usage and Options. Data Mappings
  • SSIS Input Columns, Mappings and Source Column Values
  • Data Exchange Operations with Pivot Keys and Pivot Values
  • Using Data Viewer (Debugging) for Data Transfer Verifications
  • Fuzzy Lookup Transformation, Reference Table Connections
  • Exact, Fuzzy and Nomatch Data Cleansing with Conditional Split
  • Index Creation, Maintenance for Faster Lookup Transformation
  • Threshold Values Selection, Search Delimiters and Options
  • Data Pipeline with _Similarity and _Confidence Columns. Usage
  • Data Conversion Transformation. Using NVARHAR Data, Options
  • Explicit Data Type Conversions and Usage. Error Redirections
  • ForEach Loop Container Usage. File Level Connections, Variables
  • Defining Variables for Connections. DFT and Control Flow Links
  • Connection Iterations, Connection Fetch and Index Mapping
  • Connection String Expressions with Package Variables, Usage
  • SSIS Package Audits for Dynamic Connections, Precautions
  • Identity_Insert, Table Locks and Constraints in ETL
  • Text Qualifiers and Data Loads with Flat File Sources (.dat)

Chapter 11: SSIS CUSTOMIZATION WITH SCRIPT TASK, .NET

  • Script Task - Purpose and Working in SSIS Control Flow
  • Script Task - Usage with VB.NET Programs. Compilations
  • Script Task - Usage with C#.NET Programs. Compilations
  • Variables and Parameters with SSIS Script Task. Usage
  • Read Only & Read Write Variables. Expressions, Mapping
  • Namespace Options, Customizations : System.IO, DTS
  • Using VB.NET, C#.NET Scripting Programs with Parameters
  • Data Flow Limitations : Solutions with .NET Scripting
  • SSIS Expressions and Package Debugging, Break Points
  • SSIS Packages for Database Migrations - Online/Offline
  • Database Copy and Move Options. Pre, Post Migration Tasks
  • Performing SQL Server and Database Object Operations
  • SQL Server Maintenance Tasks @ SSIS, DB Health Checks
  • Understanding Control Flow - Sequence Containers, Usage
  • Using Control Flow File System Tasks and Limitations
  • Using .Net Scripting for SQL Server Data Reads/Writes
  • SQLDataAdapters & System.Data.SQLClient Connections
  • Adding DTS Packages to SSIS Projects (Data Tools)
  • Package Refresh Options, Package Upgrade Properties
  • SSIS Configurations and Usage. Dynamic Connection Values
  • Using Variables and Parameters for SSIS Script Task
  • SSIS Package/Project Conversions - Process and Options

Chapter 5: ETL & DWH. SCD TRANSFORMATION TYPE 1, TYPE 2

  • A Sales Scenario for OLTP Database to Historical Data Loads
  • Introduction to Data Warehouse (DWH) - Purpose & Usage
  • Technical Terms: Dimensions, Attributes, Members and Types
  • Dimension Tables, Fact Tables and Relations, Inferred Members
  • TYPE1 and TYPE2 Changes. ETL Implementation Techniques
  • Implementing SCD Type1, Type 2 for DWH in a Sales Scenario
  • Dimensional Table Design for DWH using SCD - Type 1
  • Dimensional Table Design for DWH using SCD - 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
  • Naming Conventions For SSIS Entities, Options & Reusability
  • Designing Dimensions for DW - Incremental and Historical Loads
  • Understanding OLE DB Connections for Incremental Data Loads
  • Identity Property and Attribute Key Types in SCD, Limitations
  • Historical Attributes and Data Delta Operations, Identification
  • SSIS Connection Assistants - Advantages, Package Reuse
  • SSIS SCD Transformation Limitations and Real-time Issues
  • Need for Other DWH ETL Techniques for Dimension Table Design

Chapter 12: 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
  • Linking Time Dimensions to Fact Loads, Lookup Operations
  • Caching Options in Lookup. Dimension Loads, Key Lookups
  • 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
  • SCD Wizard for Multiple Dimensions and Fact Tables.
  • Parameter Mapping for ETL Updates, Component Properties
  • ETL Load Date IDs & Expressions for Dimension Keys
  • Error Handling & Event Handling with Master Packages
  • Text Qualifiers and Data Loads with Flat File Sources (.dat)
  • Project Connections, 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
  • Advantages of SSIS ETL/DWH Concepts. Precautions.
  • Column Statistics and Values for DWH Design Planning
  • STAR & SNOWFLAKE Schemas for DWH - Data Modeling

Chapter 6: CHECKPOINTS, TRANSACTION OPTIONS, SQL TASKS

  • Understanding Scripts for SSIS Control Flow. SQL Tasks
  • Execute SQL Task and OLE DB Queries - Connection Options
  • Transaction Options For SSIS Executables - Package Level
  • Precedence Constraints - Success / Failure / Completion Settings
  • Parallel and Sequential Task Executions. Options, Usability
  • 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
  • SSIS Variables - Creation and Usage in ETL Data Flow Scripts
  • Working with Static and Dynamic Variables. Usage Options
  • Containers in SSIS. Usage Options & Advantages. Properties
  • SSIS Events - Validation Events, Execution and CleanUp Events
  • SSIS Package Level Parameters and Connection Properties
  • SSIS Project Level Parameters - Connection Options, Usage
  • SSIS Expressions, Default Values, and Data Types in Parameters
  • Linking Parameters and Variables with Expression Values
  • SSIS Parameters For Dynamic Control & Package Executions
  • Dynamic Connection Managers - Precedence Constraints
  • FailPackageOnFailure Options and Checkpoint Property Usage
  • Transaction Property : REQUIRED / SUPPORT, NOTSUPPORTED
  • Transaction Property Versus CHECKPOINT Files, Usage

Chapter 13: SSIS PROJECT DEPLOYMENT and SECURITY

  • Online and Offline Deployments with ISPAK Files
  • SSIS Project Deployment Wizard : Targets and Logging
  • SSIS Package Builds, Verification Techniques, Scripts
  • SSIS Package Store, Catalog Folders and Deployments
  • Package Execution Reports, Validation Messages, Options
  • SSIS Package Security Management - SSISDB Catalog Roles
  • Security Logins, SSISDatabase Users and ssisadmin Roles
  • Understanding Folder and Project Level Security Roles
  • Execute/Write/Folder Security Content Management Roles
  • Project Migration Utilities in SSIS, Conversion Options
  • Importing ISPAC Files, SSIS Projects. Reverse Engineering
  • Command-Line Deployments For SSIS, Execution Utilities
  • Scheduling SSIS Packages (Jobs) and DB Mail Operators
  • Upgrading SSIS 2012 Packages to SSIS 2014 Catalog DB
  • Upgrading SSIS 2012 Packages to SSIS 2016 Catalog DB
  • Package Deployment: SSIS 2016, SSDT 2015 New Features
  • Package Deployment Versus Project Deployment Options
  • Version Controls - Project Exports and Imports
  • SSIS Enumerations and Lists, Dynamic Connection Loads
  • Control Loops and Indexed Connections. Precautions
  • SSIS Data Profiler Tool, ADO.NET Connections and XML
  • Advantages of SSIS ETL/DW Procedures and Data Audits
  • Using non-Microsoft Transformations : Precautions

Chapter 7: CHECKSUM TRANSFORMATION @ DWH DESIGN

  • Need for Checksum Transformation in ETL Data Loads
  • Configuring Checksum Transformation : 2014, 2016 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
  • Planning 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 Usage with Memory Loads, CAW Files (VM)
  • Memory Connections and Reuse. Lookup with Cache Options
  • Tuning Lookup Transformation: Caching Options, Index Options
  • FULL CACHE : Usage Options. NO CACHE: Usage Options
  • PARTIAL CACHE : Precautions. Data Splits, Fast Load Options
  • Cache Connection Manager Issues & Performance Tuning @ ETL
  • Pre-ETL Data Load Operations with Memory Connections
  • Lookup with NOCACHE Options. Advantages and Precautions
  • Understanding Dependent Data Flow Tasks and Usage
  • Post ETL, Derived Column and Fuzzy Transformations
  • SSIS Package Internal Parameters and Query Updates
  • OLEDB Command with Conditional Splits, Multi Row Updates

Chapter 14: REAL-TIME PROJECT IMPLEMENTATION

Real-time Project for MSBI SSIS

   ECommerce Domain: STAR, SNOWFLAKE Schemas
   ETL Implementations for DWH. Fast Loads & Sizing
   Dimension Tables, Fact Tables. Connection Parameters
   Builds, ISPAC Files. Package & Project Deployments
   Security. Scripts. Utilities, Project Conversions
   End-to-End Implementation of SSIS Development.

 

New Features in SSIS 2016
   Balanced Data Distributor,
   OData v4 data sources & and Bug Fixes
   Data Feed Publishing Components

 

WHERESPACERED and Other 3rd Party Tools
Resume Preperation
Mock Interview

 

SSIS Training Curriculum : Download Brochure

 

Benefits of our SSIS Training Course:

After the SSIS Classroom Training participant should be able to:

 
  • Completely Practical and Realtime
  • Theory material provided in Advance
  • Highly Interactive and Interesting
  • Daily Tasks and Weekly Assignments
  • Certification Guidance and FAQs
  • 24x7 Server Access with Realtime DBs
  • Design and Understand Datawarehouses (DWH)
  • Decide to use SCD, CDC and Checksum Techniques
  • Implement ETL and Data Delta with Caching Techniques
  • Handle Events and Errors with Tuning Options
  • Perform .NET Scripting and Parameterized Fuzzy Operations
  • Deploy and Implement SSIS Package Security with Jobs
 

 

Our SSIS Classroom Training Sessions are completely practical, realtime. Register Today

 

Trainer : Mr. Sai Phanindra T, 11+ Yrs of Experience exclusively on SQL Server, SQL DBA, more..

Training Reviews

Register Now

 

 

SQL Server, SQL DBA, MSBI DWH Trainings


 

[+] More Courses

Register Today

 
SQL School Phone IndiaIndia: +91 (0)9666440801
SQL School Phone USAUSA: +1(510) 400-4845
 

WHY CHOOSE US:

Microsoft Partner (ID# 4338151)

100% Real-Time and Practical

ISO 9001:2008 Certified

24/7 LIVE Server Access

Real Time Projects

Theory Material in Advance

Weekly Mock Interviews

Placement Assistance

Job Support

OFFICE 1:

#108/2RT, Road #1, Beside SR Nagar Bus Stop, Ameerpet, Hyd-38

India: 0091 (0)9666440801

Map
OFFICE 2:

Sai Anu Avenue, Street #3,
Patrika Nagar, HITEC City, Hyd - 81

India: 0091 (0)9666440801

Map

OUR RATINGS

Google: 4.7/5

Facebook: 4.8/5

Sulekha: 4.8/5

JustDial: 4.7/5

MouthShut: 5/5

UrbanPro: 5/5

Yet5: 5/5

 
 

©2008-2017 SQL School, Regd®: Sequelgate (I) Technologies Pvt. Ltd™

Sql School is an Authorized Microsoft Training Partner (ID# 4338151)

SequelGate (I) Technologies Pvt.Ltd. is rated 4.8/5 by www.facebook.com/Sqlschool based on 107 reviews

FOLLOW US:
social
social
social
social
social
social