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.