Accelerated SQL Server Integration Services

4 days / 32 hours | online / onsite

Design, develop, deploy, and operate SQL Server Integration Services (SSIS) solutions.

What Does this Course Cover?

Developed in-house by Lucient, the Integration Services (SSIS) course focuses on developing and managing SSIS 2016 in the enterprise. In this course, you will understand how to design, develop, deploy, and operate SSIS solutions—this involves ETL solutions (extraction, transformation, and loading) from source systems extractions, data integration, SSIS server administration and package execution.

In this course you will learn how to create and develop new SSIS projects and package and determine when to use project mode versus, how to apply SSIS to file and data managemen, to understand and apply ETL Concepts in SSIS including dimensions and fact table ETL and loading SSAS dimension and cubes and how to administer SSIS for server deployment and production execution.

 

Next Dates

09/18/2023 – 09/21/2023, QA, online
11/20/2023 – 14/20/2023, QA, online

 

For our classes managed by our partners QA (UK) please visit their respective websites!

Course Outline

Module 1. SSIS Overview and Core Features
  • Introduction to Business Intelligence
  • Microsoft tools for BI
  • Introduction to data integration
  • SSIS features overview
    Lab 01: SSIS Overview and Core Features
Module 2. Data Warehousing
  • Dimensional modeling
  • Optimizing a dimensional database
  • Data preparation for advanced analytics
    Lab 02: Preparing a DW
Module 3. SSIS Control Flow Objects and Features
  • What is Control Flow
  • Control Flow Concepts
  • Control Flow Objects
  • Control Flow Features
    Lab 03: Using the Control Flow to Orchestrate SSIS Execution
Module 4. Extracting, Transforming and Loading data using SSIS Data Flows
  • The SSIS Data Flow Task
  • Data Flows and Data Paths
  • Data Connections and Connection Managers
  • Data Flow Components
    Lab 04: Using Data Flows to Perform Data Movements in SSIS
Module 5. Working with Files, Importing and Exporting File Data
  • Using SSIS to Automate File System Maintenance
  • Extracting Data from Files
  • Loading Data into Files
  • Excel Considerations
    Lab 05: Working with Files in SSIS
Module 6. Optimizing Data Extraction and Data Loading
  • Data Extraction Optimization Essentials
  • Determining the „Delta“
  • Change Tracking
  • Change Data Capture
  • Data Loading Optimization Essentials
    Lab 06: Using Change Data Capture in SSIS Data Flows
Module 7. Data Quality and Cleasing
  • Data quality
  • Data profiling
  • Data Quality Services
  • Fuzzy matching
    Lab 07: Data Profiling and Cleansing
Module 8. Advanced Enterprise Information Management
  • Script task and Script Component
  • Text mining
  • Advanced Analysis and SSIS
    Lab 08: Validating Data against Regular Expressions
Module 9. Dimension ETL with SSIS
  • Dimension ETL Theory
  • SQL Server Temporal Tables
  • SSIS Slowly Changing Dimension Wizard
  • Custom Dimension ETL
    Lab 09: Dimension ETL with SSIS
Module 10. Fact ETL with SSIS
  • Fact Table ETL Theory
  • Data preparation for fact tables
  • Advanced concepts
    Lab 10: Fact ETL with SSIS
Module 11. Project Deployment Model: Execution and Reporting
  • Power Pivot
  • Power Query
    Lab 11: Power Query
Module 12. Processing SSAS Objects in SSIS
  • SSAS tabular and multidimensional
  • Processing methods in SSIS
  • Dynamic processing and partition creation
    Lab 12: Analysis Services Processing
Module 13. Project Deployment
  • Project Deployment Model
  • Deployment to the SSISDB Catalog
  • Administration, Security, Configuration
  • Execution
  • Monitoring
  • The Master Package Concept
    Lab 13: SSIS Project Deployment, Configuration, Execution, and Monitoring
Module 14. Package Deployment
  • Package Deployment Model
  • Deployment to the SSIS package store
  • Administration, Configuration, and Security
  • Execution
  • Monitoring and Logging
    Lab 14: SSIS Package Deployment, Configuration, Execution, and Monitoring
Module 15. Transactions and Restartability
  • Using Breakpoints in SSDT
  • Implementing Transactions in SSIS
  • Using SQL Server Database Snapshots
  • Restartability of SSIS Packages
  • Responding to Events
    Lab 15: Using Transactions and Checkpoints in SSIS Packages
Module 16.Optimization and Scalability
  • Leveraging SSIS and Transact-SQL
  • Data Flow Engine Internals
  • SSIS Optimization Techniques
  • SSIS Performance Troubleshooting
    Lab 16: Optimizing SSIS Packages for Performance

Matija Lah

Trainer & Author

Matija Lah, formally a university graduate in law, has been involved in information management since the 1990s. His initial introduction to SQL Server at IUS Software d.o.o. Slovenija later culminated in a new career in data-centric solutions development and consulting. His contributions to the SQL Server community have led to the award of Microsoft Most Valuable Professional in 2007 (Windows Server System SQL Server).

Dejan Sarka

Trainer & Author

Dejan Sarka focuses on database & business intelligence application development. Besides projects, he spends about half of his time on training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan Sarka is the main author or co-author of nine books on databases and SQL Server (including training kits for Microsoft exams 70-461 and 70-463). Dejan Sarka also developed several courses for Lucient. As an MCT, Dejan Sarka speaks at many local and international events. International events include conferences such as PASS, TechEd, and DevWeek. He is also indispensable at regional MS events. In addition, he is a co-organizer of the high-profile technical conference Bleeding Edge.