Ir al contenido principal

Implementing ETL with SQL Server Integration Services (SSIS)

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.

Prerequisites

A familiarity with Basic SQL Query and Transactional statements (select, insert, update, delete). Ability to write and execute statements using SQL Server Management Studio. A basic understanding of the Business Intelligence process.

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

0 | Module 0 - Start Here

  • Welcome to DAT217x: Implementing ETL with SQL Server Integration Services (SSIS)
  • Setting Up Your Lab Environment in Azure

1 | Module 1 - ETL Processing

  • ETL Overview
  • ETL Tools
  • Data Sources and Destinations
  • Creating Example Databases
  • Module Assessment
  • Module Summary

2 | Module 2 - ETL with SQL Programming

  • ETL Scripts
  • Transformations
  • Implementing ETL with SQL Objects
  • Lab | Programming an ETL Process with SQL
  • Module Assessment
  • Module Summary

3 | Module 3 - ETL Processing with SSIS

  • Creating SSIS Projects
  • Programming SSIS Packages
  • Execute SQL Tasks
  • Implementing SSIS Packages
  • Lab | Creating an ETL Process with SSIS
  • Module Assessment
  • Module Summary

4 | Module 4 - SSIS Data Flows

  • Creating Data Flows
  • Data Flow Transformations
  • Performance Options
  • Lab | Creating an SSIS ETL Package Using Data Flow Tasks
  • Module Assessment
  • Module Summary

5 | Module 5 - Deployment and Troubleshooting

  • Error Handling
  • Deployment
  • Module Assessment
  • Module Summary

6 | Module 6 - Final

  • Final Assessment
  • Bonus Video
  • Post-Course Survey

Meet the instructor

Course Staff Image #1

Randal Root

Lecturer

University of Washington

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 #1

Cari Mason

Content Developer

Microsoft

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 #1

Joey Chemis

Data Scientist and Software Engineer

Microsoft

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).

Enroll