PRE-REQUISITES 

Participants should be familiar with basic RDBMS concepts and SQL queries. Working experience on any RDBMS would be an added advantage. 

Overview SSIS 

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. 

Course Objectives 

• • 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. 

SSIS 

Contents 

DAY 1 

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 

DAY 2 

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 

DAY 3 

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.