SSIS – SQL Server Integration Services

Module 1: SSIS Architecture & Setup

• SSIS architecture: packages, containers, tasks, data flows
• SSIS vs ADF: on-premise vs cloud ETL
• SQL Server Data Tools (SSDT): development environment
• SSIS Catalog (SSISDB): deployment and execution model
• Creating first SSIS package: control flow and data flow
• SSIS deployment models: project and package

Module 2: Control Flow Tasks

• Execute SQL Task: running queries and stored procedures
• File System Task: file and folder operations
• Script Task: C# and VB.NET custom logic
• Send Mail Task: email notifications
• Execute Process Task: running external commands
• For Loop, Foreach Loop, and Sequence containers

Module 3: Data Flow Components – Sources

• OLE DB Source: SQL Server and OLEDB database sources
• Flat File Source: delimited and fixed-width files
• Excel Source: reading Excel workbooks
• ADO.NET Source: .NET-connected database sources
• XML Source: reading XML data files
• ODBC Source for generic database connectivity

Module 4: Data Flow Transformations

• Derived Column: calculated and expression-based columns
• Conditional Split: row routing based on conditions
• Lookup: reference data join in data flow
• Aggregate: GROUP BY and aggregate calculations
• Sort: data ordering transformation
• Data Conversion: type casting in data flow

Module 5: Advanced Transformations

• Merge and Merge Join: combining sorted data flows
• Union All: combining multiple inputs
• Slowly Changing Dimension (SCD): Type 1, 2, 3 handling
• Fuzzy Lookup and Fuzzy Grouping: data quality
• Term Extraction and Term Lookup: text mining
• Pivot and Unpivot: row/column restructuring

Module 6: Variables, Parameters & Expressions

• SSIS Variables: storing runtime values
• Package and project parameters
• SSIS expressions: dynamic configuration of properties
• Property expressions: dynamic connection strings and paths
• Precedence constraints with expressions
• For Each File Enumerator: dynamic file processing

Module 7: Deployment & Execution

• Project deployment to SSIS Catalog
• Execution in SSMS: manual and SQL Agent execution
• SQL Server Agent: scheduling SSIS packages
• Package execution parameters and logging
• SSIS Catalog reports: execution history and performance
• dtexec: command-line package execution

Module 8: Error Handling, Logging & Performance

• Event handlers: OnError, OnTaskFailed, OnPostExecute
• Error output routing: capturing bad rows
• SSIS logging: built-in and custom log providers
• Performance tuning: buffer sizes and DefaultBufferMaxRows
• Fast Load option for OLE DB Destination
• Integration with ADF: running SSIS in Azure Data Factory

Our SSIS instructors are Microsoft-certified data platform specialists with extensive SSIS development experience for SQL Server data warehouses, data migrations, and BI platforms. They bring deep knowledge of SSIS performance tuning, SCD implementation, and SSIS Catalog management in enterprise environments.