Skip to main content

Implementing ETL with SQL Server Integration Services

About This Course

Microsoft SQL Server Integration Services (SSIS) features graphical tools and wizards for:

  • building and debugging packages
  • tasks for performing workflow functions
  • executing SQL statements
  • data sources and destinations for extracting and loading data
  • transformations for cleaning, aggregating, merging, and copying data

In this computer science course, you'll extract data from a wide variety of data sources, such as files and relational data sources, perform data integration and transformation solutions, and load data into single or multiple destinations. You will solve complex business problems using best practices and troubleshooting techniques.

What you'll learn

You’ll learn design principles and solutions for:

  • Deciding on full or incremental loading
  • Isolating ETL data
  • Creating an ETL script
  • ETL abstraction layers
  • The design environment
  • Control flows
  • Data sources
  • Data transformations
  • Data destinations
  • Precedence constraints
  • Connection managers
  • Execute SQL tasks
  • Progress/execution results
  • Resetting destination database
  • Data flows
  • Data flow paths
  • Error output paths
  • Configuring data sources and destinations
  • Executing SSIS packages
  • Deploying SSIS packages

Course Syllabus

1 | Module 1 - ETL Processing

ETL Overview

  • Course Overview
  • Extracting Data
  • Transformations
  • Loading Data
  • Simple ETL Processing

ETL Tools

  • SQL Server Management Studio
  • Visual Studio
  • SSIS Projects
  • ETL Processing with SSIS

Sources and Destinations

  • Files
  • Databases
  • Web Services
  • Sources and Destinations

Creating an Example Database

  • Creating an Example Database
  • Creating the Source Database
  • Creating the Destination Database

Module Assessment

  • Simulation
  • Module Summary

2 | Module 2 - ETL with SQL Programming

ETL Scripts

  • Overview
  • Flush and Fill
  • Incremental Loading

Transformations

  • Name and Datatype Transformations
  • Transforming data values
  • Handling Date and Time Data
  • Handling Nulls
  • Incremental Loading with SQL Merge

Implementing ETL with SQL

  • Using the Query Designer
  • Creating ETL Views and Stored Procedures
  • Creating a SQL ETL Script

Module Assessment

  • Simulation
  • Module Summary

3 | Module 3 - ETL Processing with SSIS

Creating SSIS Projects

  • Overview
  • SSIS Packages

Programming SSIS Packages

  • Sequence Containers and Precedent Constraints
  • Outlining an ETL process with SSIS
  • Configuring connections
  • Execute SQL tasks
  • Using Stored Procedures from SSIS

Implementing SSIS Packages

  • Resetting your Destination Database
  • Testing an SSIS package

Module Assessment

  • Simulation
  • Module Summary

4 | Module 4 - SSIS Data Flows

  • Creating Data Flows
  • Overview

SSIS Data Sources, Transformations, and Destinations

  • Data Flow Paths
  • Data Viewers
  • Data Flow Transformations
  • Sort and Data Conversion
  • Aggregate and Derived Columns
  • Lookups
  • Merge, Merge Join, and Union All
  • Performance Options
  • Tuning Data Sources
  • Staging Databases

Module Assessment

  • Module Summary
  • Module Assessment

5 | Module 5 - Deployment and Troubleshooting

Testing

  • Event Handlers
  • Logging SSIS packages
  • Troubleshooting

Deployment

  • Deploying to the Network
  • Deploying to SQL Server
  • ETL Automation

Module Assessment

  • Simulation
  • Module Summary

6 | Module 6 - Final

  • Course Summary
  • Final Exam

Meet the instructors

Course Staff Image #1

Randal Root

Randal is a senior executive consultant specializing in .NET Programming and SQL Server BI solutions. Randal has worked in the industry as a solution designer, network administrator, DBA, and programmer since the 1980s. His passion is to impart knowledge. For the last 18 years, he has provided IT training for businesses and schools; including Microsoft, the University of Washington, and various community colleges in the Seattle area. Randal is an author of two books: A Tester's Guide to .NET Programming and Pro SQL Server 2012 BI Solutions. Randal has achieved several Microsoft professional certifications include MCSE, MCP+I, MCTS, MCDBA, and MCAD.

Course Staff Image #2

Cari Mason

Cari is a content developer at Microsoft. Her current role involves developing, producing and delivering Data Analytics courseware. Her experience includes developing content for IT Professionals and developers with a major focus on Business Intelligence Solutions. She has worked with the University of Washington’s continuing education programs, assisting with C# programming, web development, and BI courses. Her passion is in education and diversity, with a special interest in Autism support and career growth. She is an author of Pro SQL Server 2012 BI Solutions.

Course Staff Image #2

Joey Chemis

Joey Chemis is a Data Scientist and Software Engineer at Microsoft with a focus on predictive analytics. His work involves forecasting projects that enable him to assist First Party Devices and the Commercial Team in demand planning, while using development tools such as R, Power BI, Azure Machine Learning, SQL Server, and Excel. His passion is mathematics and statistics and the heavy role they play in the field of Data Science. Prior to Microsoft, he worked as a Data Scientist at REI. Joey has completed the P-Probability requirement and is working on achieving the designation Associate of the Society of Actuaries (ASA).

  1. Course Number

    DAT217x
  2. Classes Start

  3. Classes End

  4. Estimated Effort

    12-24 hours in total
Enroll