DAY 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
- SSIS 2016 and SSIS 2014 : SSIS Database Installations
- Understanding SSIS Catalog DB Encryptions and CLR / Startup
- SSIS Database and Catalog Folders - Purpose, Usage, Passwords
- SSIS 2016 Configuration Options and DB Catalog Encryptions
- 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 - Tools Usage
- SSIS video Training - Lab Plan, Resources & Databases
- SSIS video Training - Real-time Project, Certification (70-767)
|
DAY 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
- 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 : 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
- Using non-Microsoft Transformations : Precautions
|
DAY 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
- 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
- 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 64 Bit and 32 Bit Configuration Settings and Options
- Error Outputs and Error Row Redirections in Data Flow Tasks
- Data Flow Truncations and Error Pipelines. Transformation Types
|
DAY 8: CHANGE DATA CAPTURE: DWH DIMENSIONS
- 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 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 Row Updates
- Integrating Control Flow and Data Flow for CDC @ ETL
- CDC Splitter Transformation - Row Inserts and Updates
- CDC Splitter Precautions, Input Types and Output Range
- Connection Parameters, Master - Child Parameter Binding
- Parent - Child Package Design in SSIS, Options
- Master Packages and Child Packages, Derived Columns
- Defining Local and Global Variables/Parameters
- Local Connections - Package Level. Naming Conventions
- Global Connections - Project Level. Naming Conventions
- Parameter Bindings with Parent - Child Packages in SSIS
|
DAY 3: SSIS FAST LOADS, MERGE, DATA CONVERSIONS
- Understanding ETL and DWH Implementations. DWH Strategies
- Kimball Method & Inmon Methods of BI Design & Modelling
- Bulk Load Operations and Data Import Options in SSIS
- Bypass Prepare and Execution Options with SQL Task
- Historical Data Loads (Type II) 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 and 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 with SSIS Packages
- Identity_Insert, Table Locks and Constraints in ETL
- Comparing Regular Loads and Fast loads in ETL / DW
|
DAY 9: XML, LOGGING & ERROR HANDLING
- 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
- ADO.NET Connections For Data Audits, ETL DWH Design
|
DAY 4: PIVOT, FUZZY LOOKUP, DATA CLEANSING, LOOPS
- Importing Legacy Data, Need for Data Cleansing, Formatting
- Understanding Denormalization and Keys. 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
- Lineage IDs for Column Mapping and Pivot Keys
- 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
- Debugging with Detach/Attach, Memory Buffer & Row Counts
- Fuzzy Lookup Transformation, Reference Table Connections
- Exact, Fuzzy and Nomatch Data Cleansing with Conditional Split
- Index Creation, Maintenance for Faster Lookup Transformation
- Data Conversion Transformation - Usage. Derived Columns
- Data Conversion Transformation For NVARHAR & VARCHAR Data
- Threshold Values Selection, Search Delimiters and Options
- Data Pipeline with _Similarity and _Confidence Columns. Usage
- 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
- Dynamic Connections with Variables. Connection String Properties
- Connection Iterations, Connection Fetch and Index Mapping
- MS Excel and OLE DB Jet Driver Connections For Data Cleasing
|
DAY 10: SSIS CUSTOMIZATION WITH .NET SCRIPT
- 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 and 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 Variables and Parameters - Mapping Expressions
- 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
- Script Task for Data Writes and Row Level Formatting
- Adding DTS Packages to SSIS Projects (Data Tools)
- Package Refresh Options, Package Upgrade Properties
- SSIS Configurations, Usage. Dynamic Connection Values
- Using Variables and Parameters for SSIS Script Task
- Script Component in Data Flow Task - Usage Options
- Script Component - Input, Output and Transformations
- Using Script Task for Control Flow, Data Flow Limitations
- SSIS Package/Project Conversions - Process and Options
|
DAY 5: SSIS with ETL & DWH. SCD TYPE 1, TYPE 2
- A Sales Scenario for OLTP Database to Historical Data Loads
- 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 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
- Using SCD Transformation with OLE-DB Command Transformation
|
DAY 11: 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
|
DAY 6: CHECKPOINTS, TRANSACTIONS, EXECUTE SQL
- 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
|
DAY 12: SSIS PROJECT DEPLOYMENT, UPGRADES
- SSISDB Catalog Deployments - Process, ISPAC File Structure
- SSIS Package Builds, Verification Techniques, Scripts
- SSIS Project Deployment Wizard : Targets and Logging
- SSIS Package Deployments - Need, Options and Tools
- 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 from SQL Server Agent. SSIS Job Steps
- SSIS Job Schedules and Notifications: Windows Event Logs
- SQLISPackage130 Events and Error Logs. Package Monitoring
- Package Security Management - SSISDB Logins and Users
- ssis_admin, ssis_failover_monitoring_agent, ssis_logreader
- Folder Level and Project Level Security Implementation
- Execute/Write/Folder Security Content Management Roles
- Business Intelligence Development Studio: SSIS BIDS 2008
- Creating Packages, SMO Connection with BIDS. Project Builds
- Adding DTS, SSIS 2012 Packages & Package UPGRADES
- Project Migration Utilities in SSIS, SSIS Project Passwords
- Importing ISPAC Files, SSIS Projects. Re-Engineering
- Command-Line Deployments For SSIS, Execution Utilities
- SSIS Packages for Database Migrations - Online/Offline
|
DAY 13: 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.
WHERESPACERED and Other 3rd Party Tools
Resume Preparation
Mock Interviews
|
