Duration: 3 days – 21 hrs
Overview
The Basic SQL Server Integration Services (SSIS) training course is designed to provide participants with a comprehensive understanding of the fundamental concepts and practical skills required to develop and manage data integration solutions using SSIS. This instructor-led course will cover the essential aspects of SSIS, including data extraction, transformation, and loading processes, enabling participants to become proficient in building robust and efficient ETL (Extract, Transform, Load) workflows.
Objectives
- Understand the fundamental concepts and architecture of SQL Server Integration Services (SSIS).
- Gain proficiency in using the SSIS development environment and tools.
- Learn to design and implement ETL workflows for data extraction, transformation, and loading.
- Acquire the skills to handle data flow tasks, control flow tasks, and event handling in SSIS.
- Explore various data sources, destinations, and transformations available in SSIS.
- Learn best practices for error handling, logging, and package deployment in SSIS.
- Understand how to troubleshoot and optimize SSIS packages for improved performance.
- Gain practical knowledge in handling real-world data integration scenarios using SSIS.
Audience
- Database administrators
- Database developers
- ETL developers
- Business intelligence professionals
- Data analysts
Pre- requisites
- Basic understanding of databases and SQL concepts
- Familiarity with SQL Server Management Studio (SSMS)
- Knowledge of SQL queries and data manipulation
Course Content
Day 1: Introduction to SSIS and Data Integration Concepts
Introduction to SSIS
- Overview of SQL Server Integration Services (SSIS)
- Understanding the purpose and benefits of SSIS
- Exploring the SSIS development environment (SSDT)
SSIS Architecture and Components
- Overview of SSIS architecture and its key components
- Understanding the Control Flow and Data Flow components
- Working with packages, tasks, and connections
Creating SSIS Packages
- Creating a new SSIS package
- Configuring package properties
- Adding and configuring tasks in the Control Flow
- Data Flow in SSIS
- Introduction to the Data Flow Task
- Working with data sources and destinations
- Transforming and manipulating data with transformations
Day 2: Data Transformations and Control Flow
Data Transformations in SSIS
- Understanding the various data transformations available in SSIS
- Using transformations like Derived Column, Conditional Split, Lookup, and more
- Implementing data conversions and data cleaning operations
Control Flow in SSIS
- Working with control flow tasks (Execute SQL Task, File System Task, etc.)
- Using precedence constraints to control the execution flow
- Configuring containers (Sequence Container, For Loop Container, etc.)
Error Handling and Event Handling
- Implementing error handling in SSIS packages
- Configuring error outputs and redirecting rows
- Using event handlers to respond to package events
Package Configurations and Deployment
- Configuring SSIS package properties using package configurations
- Working with different configuration types (XML, environment variables, SQL Server)
- Deploying SSIS packages to SQL Server or the file system
Day 3: Advanced SSIS Topics and Package Deployment
Advanced Data Flow Transformations
- Exploring advanced data flow transformations (Fuzzy Lookup, Pivot, Unpivot, etc.)
- Using SSIS expressions to build dynamic packages
- Implementing data sampling and sorting operations
Variables and Parameters in SSIS
- Working with variables and data types in SSIS
- Configuring package and project parameters
- Using variables and parameters in expressions and script tasks
Debugging and Troubleshooting SSIS Packages
- Debugging SSIS packages in Visual Studio
- Logging package execution and reviewing logs
- Troubleshooting common SSIS package issues
SSIS Package Execution and Scheduling
- Running SSIS packages manually using SSDT
- Scheduling package execution with SQL Server Agent
- Monitoring and managing package execution using SQL Server Integration Services Catalog
