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