Ir al contenido principal

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
Inscribirse