FastTrack SQL Server 2012 / 2014 Integration Services

Course#: BSSQL1002

About this Course

Course Type Course Code    Duration
SQL BSSQL1002 4 Days

This course 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.

Why Attend this Course?

This is a highly interactive hands on course.

What Makes this Course Stand Apart?

Delivered in small group size to maximise the learning experience.

Available with Plus1 Accelerator Option.

What you will Learn?

Audience

This course is intended for SQL professionals.

Prerequisites

This course is targeted at database professionals and developers with some experience in business intelligence solutions and SQL Server.

Course Outline

SSIS Overview and Core Features

  • How SSIS fits into the corporate data network and infrastructure
  • Introduction to data integration, BI, and dimensional modeling
  • SSIS features overview
  • SQL Server 2012 tools

SSIS Development Environment Introduction

SSIS Control Flow Objects and Features

  • Control flow and data flow principles
  • Control flow features
  • Containers
  • SSIS expression language
  • Tasks and constraints

Workflow Management in the Control Flow

Applying Data Flow Transformations and Adapters

  • Data flow introduction and design environment
  • Connecting the data flow to sources and destinations
  • Transformations and paths

Working with Data in the Data Flow

Applying SSIS to Common Operations

Working With and Importing Files

  • File-handling requirements and management
  • Working with binary data
  • Excel and 64-bit considerations
  • Third-party custom components

Importing and Processing FTP Sourced Files

Data Source Extraction and Destination Optimization

  • Extraction optimization
  • Incremental extraction and Change Data Capture
  • Data loading optimization

Incremental Extraction and ODBC Sources

Data Quality and Cleansing

  • Data profiling and cleansing basics
  • Fuzzy operations
  • Fuzzy Lookup
  • Fuzzy Grouping
  • Text mining
  • Script component
  • Data Quality Services

Comparing the Fuzzy Lookup and DQS Cleansing Transformations

Applying SSIS in BI and Data Warehouse Solutions

Dimension ETL with SSIS

  • Fact table ETL theory
  • Fact loading concepts, columns, and mappings
  • Workflow, precedence, and staging
  • Identifying dimension surrogate keys
  • Data preparation for fact tables
  • Advanced concepts

Dimension Table ETL

Fact 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

Fact Table ETL

Processing Tabular and Multidimensional SSAS Objects in SSIS

  • SSAS tabular and multidimensional
  • Processing methods in SSIS
  • Dynamic processing and partition creation

Analysis Services Processing

Project Deployment Model

Project Deployment Model: Configuration, Deployment & Security

  • Project Deployment Model
  • SSIS catalog features
  • Project and package connections
  • Project and package parameters
  • Project Deployment Wizard
  • SSIS environments
  • Security and encryption

SSIS Catalog Configurations and Project Deployment

Project Deployment Model: Execution and Reporting

  • Project deployment model utilities
  • Package execution with T-SQL and DTExec
  • Server reporting and logging

Executing Packages in the SSIS Catalog

Package Deployment Model

Package Deployment Model: Configuration and Deployment

  • Package deployment model
  • Package configurations
  • Deploying packages

Working with Package Configurations

Package Deployment Model: Execution, Security & Logging

  • Package deployment model utilities
  • Package execution
  • Package security and encryption
  • Package logging

Package Logging and Execution

SSIS Solution and Performance Considerations

Transactions and Restartability

  • SSIS transactions
  • SQL Server database snapshots
  • Restartability with checkpoints
  • Debugging with breakpoints

Working with Transactions and Checkpoints

Optimization and Scalability

  • SQL code versus SSIS pipeline
  • Data flow engine internals
  • General optimization techniques
  • Performance root-cause analysis

Identifying and Resolving SSIS Performance Issues

Solution Approach and Team Development

  • Modular and parent package design
  • Team development considerations

Creating a Parent Package

What next- How do I arrange a group course or book a public place.?

We are hear to to help so please utilise our live chat team

Call to speak to your account manager or a consultant on +44 (0) 345 467 9557 or email sales@brightts.com

We are all technical with a wealth of Learning & Development experience so can talk you through any specific requirements or the details of one of our courses.

0

Start typing and press Enter to search