close× Call Us +1 (480) 386-5326
close×

SQL Server Integration Services - A Practical Approach

The “SQL Server Integration Services – A practical approach” course builds on the beginner and intermediate skills you have already mastered either on the job or via personal development. In this course, you will learn how to Create, Maintain, Extract Transform and Load processes using SSIS. You will also learn how to create medium to complex workflows to achieve the highest level of automation with SSIS. This is a very practical course – you will be exposed to real live business processes and define optimal solutions to transform and migrate data.

Target Audience
  • Beginners (Completely new to SSIS)
  • Experienced developers willing to enhance their SSIS Skills
Delivery Format
  • Both in-class and online class options will be available
  • Homework is assigned after every class
  • Students are grouped into teams to work on special projects
  • Project is due 2 weeks after course completion
Cost & Payment Terms

The cost of this 8 week course is $1,200.


There are 2 payment options:
  • Option 1: Full payment due at the beginning of the course.
  • Option 2: Half due at the beginning of the course and balance due by the start of week 4.

Discounts are available to those who qualify

Course Dates & Duration

The course duration is 40 hours, spread over a period of 8 weeks. Classes will be held on Saturdays Tentatively scheduled to hold between the hours of 12 Noon to 3 PM MST (3 hours per session)

Prerequisites

Knowledge of Microsoft Windows operating systems.
Basic understanding of database design and concepts Working knowledge of SQL queries


Course Outline

Part I: Data Warehousing Fundamentals (4 hours)
Data Warehousing Overview
  • Why a data warehouse?
  • Dimensional modeling - Overview
  • Data warehousing methodologies
  • Data storage concepts
    • Operational Data Store
    • Data mart
Part II: Data Integration (Extract, Transform and Load) with SSIS (32 hours)
SSIS Overview & Setup
  • SSIS Architecture
  • Configuring Visual studio for ETL package development
  • Designers
    • Control flow, Data flow, Event handlers, Parameters, Package explorer
  • Windows
    • Connection Manager, Variables, Output, Error List, Properties, SSIS Toolbox
Data Integration Using SSIS
  • Understanding Connection mangers
    • OLEDB, ODBC, Flat File, File, Excel, ADO.NET, SMTP, HTTP
  • Control flow basics
    • Understanding tasks , precedence constraints, containers , variable, annotations and parameters (Package & Project)
  • Designing solutions using:
    • Execute SQL, FTP , Expression , Send Email, Execute Package, Execute process, File system, Script and Web service tasks
    • Expressions
    • Debugging SSIS variables , parameters and scripts during runtime (breakpoints)
  • Data flow basics
    • Data flow tasks
    • Connection assistants
    • Data extraction and Load using:
      • OLEDB, ADO.NET, ODBC, XML, Flat file
    • Data transformation using:
      • Row Count, Audit, Copy Column, Import column, Derived Column, Data Conversion, Conditional Split, Multicast, Aggregate, Lookup , Sort, Merge, join, Union All , OLEDB Command, Pivot and Unpivot
    • Debugging data migration using data viewer
Error handling in SSIS
  • Error handling in Control flow tasks
    • Precedence constraints
    • Maximum error counts
  • Error handling in data flow tasks
    • Precedence constraints
    • Redirect rows
Logging and Alerting in SSIS
  • Understanding in built logging functionalities in Excel
    • Logging to text files, logging to SQL Server
  • Building custom logging in SSIS (SQL Server tables)
  • Event bubbling (Propagation)
  • Building customized alerting solutions in SSIS
Part III: SSIS Deployment and Monitoring (4 hours)
Deployment models
  • Package Deployment model
    • File system deployment
    • SQL Server deployment
  • Project deployment model
    • Integration Services Catalog & SSISDB
    • Setting up environments
Deploying SSIS Packages
  • Deploying packages using file system
  • Deploying packages using SQL Server (MSDB)
  • Deploying packages using integration services catalog
  • Scheduling package execution using SQL Server agent jobs
  • Executing packages using stored procedures
Monitoring SSIS Packages
  • Introduction to reports (IS Catalog)
  • SSISDB configuration tables
Part IV: Special Projects
  • Data warehouse incremental load using Change Data Capture
  • Loading transactional tables from web services
  • Database maintenance and analysis services cube processing using SSIS
  • Implementing Slowly changing dimensions using SSIS