• Duration
    • 4 Days
  • Price
    • AU $2,800 + GST*

*NOTE: There is no GST charged for New Zealand businesses

Description

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 extraction, transformation, and loading (ETL) solutions 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.

Prerequisites

Before attending this course, it is recommended that students have the following skills:

  • 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

Course Objectives

Upon completion of this course, the student will be able to:

  • Create and develop new SSIS projects and packages
  • Determine when to use project mode versus
  • Apply SSIS to file and data management
  • Understand and Apply ETL Concepts in SSIS including dimensions and fact table ETL and loading SSAS dimension and cubes
  • Administer SSIS for server deployment and production execution

Course Summary Outline

DAY 1

Section A: SSIS Overview and Core Features

Module A-Overview: Course Introduction, Integration Services in SQL Server 2012 Features, Design Environment Overview

  • SQL Server 2012 installation and tools, SSIS features and architecture, the role of SSIS for the DBA, Working with and Deploying SSIS Projects, Properties windows and viewers, Data Sources, Data Source Views, Project Connections, Package variables and Project Parameters | Lab A-Overview: Creating and Deploying an SSIS Project and Package

Module A-Control Flow: SSIS Control Flow Objects and Features

  • Control Flow Task review, Execute SQL Task and Parameters, Sequence Container, Looping Containers, Standard and Expression based Constraints | Lab A-Control Flow: Workflow Management in the Control Flow

Module A-Data Flow: Applying Data Flow Transformations and Adapters

  • Data Flow source and destination adapters, Data expressions and conversion transformations, Data association and correlation transformations (IE: Lookup, Merge), Data Paths and Data Viewers | Lab A-Data Flow: Working with Data in the Data Flow

DAY 2

Section B: Applying SSIS to Common Operations

Module B-File Management: Working with and Importing Files

  • Drilling into File manipulation components: FTP Task, XML Task, Bulk Insert Task, File System Task, For Each Loop Container | Lab B-File: Building an FTP Solution

Module B-Extraction: Data Source Extraction and Destination Optimization

  • Source Adapters, Data extraction practices, Destination Adapters, Data loading optimization | Lab B-Ext: Data Sources and Destinations

Module B-Data Cleansing: Data Quality and Cleansing

  • Using the Data Profile Task, Working with Data Quality Services, Applying the Fuzzy Lookup and Fuzzy Grouping transformations, Parsing words from text columns | Lab B-Data: Data Quality and Cleansing

Module B-Event: Notifications, Windows Management Instrumentation, Event Handlers and File watching

  • Leveraging the Send Mail Task, Introduction to WMI, Installing the custom file watcher component | Lab B-Event: Notifications, WMI, and Event Handlers

DAY 3

Section C: Applying SSIS in BI and Data Warehouse Solutions

Module C-Dim: Dimension ETL with SSIS

  • Slowly Changing Dimension types and theory, Using the SCD wizard, Drilling into the SCD transformations, Customizing the SCD, Alternate SCD approaches, Inferred Members, Handling Snowflake and Parent-Child Hierarchies | LAB C-Dim: Dimension Table ETL

Module C-Fact: Fact Table ETL with SSIS

  • Fact types and theory, Aspects of the fact table processing, Dimension lookups with the Lookup transformation, Missing Dimension Members, Measures and Calculations, Handling fact inserts and updates, Changing data grain, Processing Analysis Services Measure Group Partitions | LAB C-Fact: Fact Table ETL

Module C-SSAS Multi-Dimensional: Processing Tabular and Multi-Dimensional SSAS Objects in SSIS

  • Analysis Services processing types, Analysis Services Processing Task and Execute DDL Task, Dynamically creating and processing partitions, ASCMD command line and AMO API usage | LAB C-SSAS: Analysis Services Processing

Section D: Package Administration and Management

Module D-Package Mode: Legacy Package Deployment Mode and Logging

  • Planning your package configuration environment, SSIS Configuration types, Legacy Package Deployment Extending SSIS, Package Mode Logging, Executing and scheduling packages in Package Deployment Mode | Lab D-Package Mode: SSIS Package Mode Logging and Configurations

Module D-Project Mode: Project Deployment Mode and Server Reporting

  • Planning your SSIS server environment, Using the SSIS Server Logging, Executing Packages and Solutions in the SSIS Server, Executing Packages with SQL Agent, SSIS Environments | Lab D-Server: SSIS Project Mode Deployments

Module D-Dynamic Properties: Project Parameters, Variables and Expressions

  • Planning your project environment, Variables and Dynamic Property Expressions, SSIS Server Reporting and Logging, Extending SSIS auditing with Event Handlers | Lab D-Admin: SSIS Logging and Configurations

DAY 4

Module D-Transactions and Security: Understanding SSIS Transaction, Restartability, and Security

  • Built-in checkpoints, Managing RDBMS transactions, Rollback considerations, Integrating Checkpoints with Transactions | Lab D-Tran: Using Transactions and Checkpoints

Section E: Optimization and Team Development

Module E-Optimization: Optimizing and Scaling SSIS Projects

  • Identifying package bottlenecks, Understanding the SSIS data flow architecture, Pipeline logging, Debugging Task with Breakpoints, SSIS Performance Monitor counters and tracking | Lab E-Scale: Troubleshooting and Optimizing an SSIS Package

Module E-Team: Enterprise Development and Scaling

  • Working in a team environment, Enterprise solution considerations, Hardware planning, Partition Table management

Location Details

Auckland

 Mentor Paco Gonzalez

Other SolidQ Courses