Accelerated SQL Server Integration Services

• Create and develop new SSIS projects packages.
• Apply SSIS to file and data management.
• Loading SSAS dimensions and cubes.
• Determine when to use projects mode versus.
• Understand and apply ETL concepts in SSIS.
• Administer SSIS for sever deployment and production execution.

• Basic experience in business intelligence solutions and SQL Server
• No prior experience with SQL Server SSIS is required

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