SQL Server Integration Services (SSIS) Advance

This course is designed to equip students with the skills necessary to build SSIS (SQL Server Integration Services) packages for extracting, transforming, and loading (ETL) operations within a data warehouse environment. Throughout the course, students will learn methodologies for managing high-volume data and slowly changing dimensions, ensuring they can handle complex and dynamic datasets effectively. Additionally, the course covers techniques for processing and integrating XML files, a crucial skill for modern data management.

Students will gain in-depth knowledge of design patterns essential for loading data into dimension and fact tables, which are critical components of data warehousing. The course will also guide students through the deployment process, ensuring they can transition their SSIS packages from development to production smoothly. Finally, students will learn how to execute these packages efficiently, maintaining optimal performance and reliability in a production setting.

By the end of the course, students will have a comprehensive understanding of ETL processes, data management strategies, and practical skills in SSIS, preparing them to tackle real-world data warehousing challenges confidently.

This course is ideal for people who already know the basics of SQL Server Integration Services (SSIS).

1: Advanced Integration Services – I

• Using XML Files
• Using Web Service in SSIS
• 32 bit Vs 64 Bit
• SQL Task to return single / multiple columns / record set
• Using SQL Task in Control flow

2: Advanced Integration Services – II

• SSIS Expressions
  • Expression Introduction
  • Expression Builder & Functions
  • Using expression in precedence constraint
  • Operators and More Variables
  • Variables
  • Expression Usage
    • Connection Manager
    • SQL Statement
    • Precedence Constraints
• Debugging SSIS Packages
  • Debugging – Basics
  • Breakpoints
  • Debug Windows
  • Script Task Debugging
  • Script Component Debugging
  • Data Viewer
  • Using the Grid Viewer
• Logging & Error Handling in SSIS Packages
  • Package Logging
  • Different types of logging using SQL server, Flat file, XML
  • Logging in Control flow and Script Task
  • Error output in Data Flow task
  • Transaction Support
  • Checkpoints
• Package Execution
  • Package execution utilities
  • DTEXEC
  • SQL Server Agent

3: Introduction to Data Warehouse

• Data Warehouse Basics
  • OLTP Vs OLAP
  • Star Schema / Snow flake Schema
  • Kimball Vs Bill Inmon
  • ETL Vs BI
  • ETL Tools
• Dimension
• Facts
• Summary

4: Developing SSIS Packages for Data Warehouse – I

• ETL Design pattern
• Extract package
• Extracting to staging
• Load Patterns
• Fact extract for ongoing load

5: Developing SSIS Packages for Data Warehouse – II

• Slowly changing dimension
• Dimension loading pattern for Type 0, Type 1 & Type 2
• Dimension Load Design patterns
• Fact table load design pattern

Our aim is not just to get you the qualification but also guide you through all processes while you are applying for your SIA Licence. Following are some of the reasons why you should choose us as your training provider:

• Excellent Success Rate!
• Fully accredited and authorized training center in London!
• Most competitive prices with NO HIDDEN COSTS!
• Prices are all inclusive of tuition, handbook, notebook, exams and certificate costs!
• Nationally recognized and Ofqual accredited training course!
• Highly skilled and qualified trainers with a great wealth of practical knowledge & experience!
• Post-qualification email support while you are preparing to book your licence application!
• Free guidance session on how to apply and obtain a SIA Licence!