Participants should be familiar with basic RDBMS concepts and SQL queries. Working experience on any RDBMS would be an added advantage.
SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server database software which can be used to perform a broad range of data migration, Data integration and Data Consolidation tasks. It features fast and flexible data warehousing tool used for data extraction, transformation and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates multidimensional cube data. SSIS is the new data transformation standard for SQL Server 2005 onwards and has replaced the old SQL Server Data Transformation Services.
• Understand MSBI application development Life Cycle.
• Understand basic Data Warehousing Concepts.
• Understand the Concept of OLTP and OLAP.
• Use SSDT (SQL Server Data Tools) in detail for development of SSIS packages.
• Learn SSIS Architecture, Components of SSIS.
• Gain complete understanding of different control flow tasks, containers and precedence constraints.
• Gain complete understanding of wide variety of data flow Sources, Transformations and Destinations.
• Learn to work with variables, data types, functions, expressions and parameters and providing Dynamism in SSIS.
• Develop packages using different tasks and different Transformations based on real time requirements.
• Learn working with event handler, Logging, Configuration Files.
• Learn Error Handling and Debugging in SSIS.
• Learn package deployment and project deployment.
• Gain understanding of Security and Package management.
Module 1: Introduction to Data Warehousing
Lesson 1: Overview of Data Warehousing
Lesson 2: Considerations for a Data Warehouse Solution
Lab: Exploring a Data Warehousing Solution
Module 2: Planning Data Warehouse Infrastructure
Lesson 1: Considerations for Data Warehouse Infrastructure
Lesson 2: Planning Data Warehouse Hardware
Lab: Planning Data Warehouse Infrastructure
Module 3: Designing and Implementing a Data Warehouse
Lesson 1: Data Warehouse Design Overview
Lesson 2: Designing Dimension Tables
Lesson 3: Designing Fact Tables
Lesson 4: Physical Design for a Data Warehouse
Lab: Implementing a Data Warehouse
Module 4: Columnstore Indexes
Lesson 1: Introduction to Columnstore Indexes
Lesson 2: Creating Columnstore Indexes
Lesson 3: Working with Columnstore Indexes
Lab: Using Columnstore Indexes
Module 5: Creating an ETL Solution
Lesson 1: Introduction to ETL with SSIS
Lesson 2: Exploring Source Data
Lesson 3: Implementing Data Flow
Lab: Implementing Data Flow in an SSIS Package
Module 6: Implementing Control Flow in an SSIS Package
Lesson 1: Introduction to Control Flow
Lesson 2: Creating Dynamic Packages
Lesson 3: Using Containers
Lab A: Implementing Control Flow in an SSIS Package
Lesson 4: Managing Consistency
Lab B: Using Transactions and Checkpoints
Module 7: Debugging and Troubleshooting SSIS Packages
Lesson 1: Debugging an SSIS Package
Lesson 2: Logging SSIS Package Events
Lesson 3: Handling Errors in an SSIS Package
Lab: Debugging and Troubleshooting an SSIS Package
Module 8: Implementing a Data Extraction Solution
Lesson 1: Introduction to Incremental ETL
Lesson 2: Extracting Modified Data
Lab A: Extracting Modified Data
Lesson 3: Loading Modified Data
Lesson 4: Temporal Tables
Lab B: Loading a Data Warehouse
Module 9: Enforcing Data Quality
Lesson 1: Introduction to Data Quality
Lesson 2: Using Data Quality Services to Cleanse Data
Lab A: Cleansing Data
Lesson 3: Using Data Quality Services to Match Data
Lab B: Deduplicating Data
Module 10: Master Data Services
Lesson 1: Introduction to Master Data Services
Lesson 2: Implementing a Master Data Services Model
Lesson 3: Hierarchies and Collections
Lesson 4: Creating a Master Data Hub
Lab: Implementing Master Data Services Model
Module 11: Extending SQL Server Integration Services
Lesson 1: Using Scripts in SSIS
Lesson 2: Using Custom Components in SSIS
Lab: Using Custom Scripts
Module 12: Deploying and Configuring SSIS Packages
Lesson 1: Overview of SSIS Development 13-2
Lesson 2: Deploying SSIS Projects 13-5
Lesson 3: Planning SSIS Package Execution 13-14
Lab: Deploying and Configuring SSIS Packages
Module 13: Consuming Data in a Data Warehouse
Lesson 1: Introduction to Business Intelligence
Lesson 2: Introduction to Data Analysis
Lesson 3: Introduction to Reporting
Lab: Using a Data Warehouse
• Discussion on migration practices from 2012 to 2017.
• Difference between SSDT 2012 and SSDT 2017.
• New Features in 2017.