Overview

This 4-day instructor led training focuses on developing and managing SSIS 2012 in the enterprise. In this course, you will understand how to design, develop, deploy, and operate SSIS solutions—this involves ETL solutions (extraction, transformation, and loading) from source systems extractions, data integration, SSIS server administration and package execution.

Target Audience

This course is intended for database professionals that are responsible for ETL or DBA activities related to data processing, data architecture planning, or SSIS administration.

The target audience for this session is IT professionals, DBAs and developers who want to learn the details of how to use SSIS to accomplish data integration, data warehouse loading, and how to administer SSIS through the development lifecycle to production.

Read more

Prerequisites

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

Learning Outcomes

  • 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
Read more

Course Outline

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
Read more

Why choose QA

Dates & Locations

Frequently asked questions

See all of our FAQs

How can I create an account on myQA.com?

There are a number of ways to create an account. If you are a self-funder, simply select the "Create account" option on the login page.

If you have been booked onto a course by your company, you will receive a confirmation email. From this email, select "Sign into myQA" and you will be taken to the "Create account" page. Complete all of the details and select "Create account".

If you have the booking number you can also go here and select the "I have a booking number" option. Enter the booking reference and your surname. If the details match, you will be taken to the "Create account" page from where you can enter your details and confirm your account.

Find more answers to frequently asked questions in our FAQs: Bookings & Cancellations page.

How do QA’s virtual classroom courses work?

Our virtual classroom courses allow you to access award-winning classroom training, without leaving your home or office. Our learning professionals are specially trained on how to interact with remote attendees and our remote labs ensure all participants can take part in hands-on exercises wherever they are.

We use the WebEx video conferencing platform by Cisco. Before you book, check that you meet the WebEx system requirements and run a test meeting (more details in the link below) to ensure the software is compatible with your firewall settings. If it doesn’t work, try adjusting your settings or contact your IT department about permitting the website.

Learn more about our Virtual Classrooms.

How do QA’s online courses work?

QA online courses, also commonly known as distance learning courses or elearning courses, take the form of interactive software designed for individual learning, but you will also have access to full support from our subject-matter experts for the duration of your course. When you book a QA online learning course you will receive immediate access to it through our e-learning platform and you can start to learn straight away, from any compatible device. Access to the online learning platform is valid for one year from the booking date.

All courses are built around case studies and presented in an engaging format, which includes storytelling elements, video, audio and humour. Every case study is supported by sample documents and a collection of Knowledge Nuggets that provide more in-depth detail on the wider processes.

Learn more about QA’s online courses.

When will I receive my joining instructions?

Joining instructions for QA courses are sent two weeks prior to the course start date, or immediately if the booking is confirmed within this timeframe. For course bookings made via QA but delivered by a third-party supplier, joining instructions are sent to attendees prior to the training course, but timescales vary depending on each supplier’s terms. Read more FAQs.

When will I receive my certificate?

Certificates of Achievement are issued at the end the course, either as a hard copy or via email. Read more here.

Contact Us

Please contact us for more information