Accelerated SQL Server Integration Services Training

• This course is targeted at database professionals and developers with some experience in business intelligence solutions and SQL Server.
• This workshop requires no prior experience with SQL Server SSIS.

• Create and develop new SSIS projects and packages
• Determine when to use project mode versus package mode
• Apply SSIS to file and data management
• Understand and Apply ETL Concepts in SSIS including dimensions and fact table ETL and loading SSAS dimens on and cubes
• Administer SSIS for server deployment and production execution

SECTION A: DW and SSIS Overview and SSIS Core Features

Module 01: 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 02: Data Warehousing

• Dimensional modeling
• Optimizing a dimensional database
• Data preparation for advanced analytics
• Lab 02: Preparing a DW

Module 03: 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 04: 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

SECTION B: Applying SSIS to Common Operations

Module 05: 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 06: 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 07: Data Quality and Cleasing

• Data quality
• Data profiling
• Data Quality Services
• Fuzzy matching
• Lab 07: Data Profiling and Cleansing

Module 08: Advanced Enterprise Information Management

• Script task and Script Component
• Text mining
• Advanced Analysis and SSIS
• Lab 08: Validating Data against Regular Expressions

SECTION C: Applying SSIS in BI and Data Warehouse Solutions

Module 09: 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

SECTION D: Deployment

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

SECTION F: SSIS Solution and Performance Considerations

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