(SSIS) SQL Server Integration Services

Duration: 3 Days

Objectives

Delegates attending this course will have requirements to implement SQL Server Integration Services (SSIS) to export and import data between mixed data sources, catering for simple exchanges and more complex requirements involving transformation of data before it reaches its destination (such as in the cases of loading a Data Warehouse). Delegates will learn how to carry out data cleansing tasks such as de-duplicating and fuzzy lookups, converge heterogeneous data sources through transformations such as merge and union, split single data sources into many with transformations such as conditional split, handle multiple import files with containers such as for and foreach loops, and much more. In this course you will learn about the Microsoft SQL Server Visual Studio development environment for design, creation and management of SSIS packages, and work with Control and Data Flows to build workflows to extract, transform, and load data using a variety of data sources, transformations, and destinations. You will also become familiar with SSIS package management and package deployment along with learning to write solid code using debugging, error handling, and logging techniques. This course, along with the (SSAS) SQL Server Analysis Services Course, helps to prepare delegates for the Microsoft 70-463 exam: Implementing a Data Warehouse with Microsoft SQL Server 2012. This exam credits towards MCSA and MCSE certifications. It should be noted, however, that all course attendances should be complemented by reference to the skills measured by the exam, a period of self-study and test exams before sitting the actual exam. This course is suitable for delegates working with all versions of SQL Server from SQL Server 2008 through to SQL Server 2016.

Pre-requisites

This course assumes no prior knowledge of SQL Server Integration Services. This course does assume prior knowledge of SQL Server database environments and the use of SQL Server Management Studio for development and administrative tasks. You should be familiar with database querying using SQL to the level of the SQL Server Database Querying Course.

Course includes

A comprehensive set of reference notes covering course topics, follow-up telephone support and, for training on Transmedia premises, lunch and refreshments.

Course outline

Introduction to SQL Server Integration Services (SSIS)

  • QL Server SSIS Historical Overview
  • SIS Architecture
  • Editions of SQL Server

The SSIS Tools

  • Import and Export Wizard
  • SQL Server Data Tools (SSDT) /Business Intelligence Development Studio (BIDS - 2008)
  • The Visual Studio Development Environment
  • The SSIS Package Designer
  • Management Studio

SSIS Tasks

  • SSIS Task Objects: Task Editor and Expressions
  • Looping and Sequence Tasks
  • Script Task (.NET)
  • Analysis Services Tasks
  • Data Flow Tasks
  • Data Preparation Tasks: Data Profiler, File System, Archiving, FTP, Web Service, XML
  • RDBMS Server Tasks: Bulk Insert Execute SQL
  • Workflow Tasks: Execute Package, Execute Process, Send Mail, WMI
  • SMO Administration Tasks: Database Object Transfer

The Data Flow

  • Understanding the Data Flow
  • Data Viewers
  • Data Sources: OLE DB, Excel, Flat File, Raw, XML, ADO.NET
  • Data Destinations: Excel, Flat File, OLE DB, Raw, Recordset, Data Mining Model Training, DataReader, Dimension and Partition Processing
  • Common Transformations: Including Aggregate, Conditional Split, Data Conversion, Derived Column, Lookup, Script Component, Sort, Union All
  • Other Transformations: Including Audit, Copy Column, DQS Cleansing, Export and Import Column, Merge, Merge Join, Multicast
  • Data Flow Example

Using Variables, Parameters and Expressions

  • Dynamic Package Objects: Variables, parameters and Expressions
  • Understanding Data Types
  • Using Variables and Parameters
  • Working With Expressions and Expression Builder
  • Operators and Functions

Containers

  • Task Host Containers
  • Sequence Containers
  • Groups
  • For Loop Container
  • Foreach Loop Container

Joining Data

  • The Lookup Transformation
  • The Merge Join Transformation
  • Contrasting SSIS and Relational Join
  • Using the Lookup Transformation
  • Cache Connection manager and Cache Transform

Creating A Data Cleansing Package

  • Creating Connections
  • Creating Control Flow
  • Creating Data Flow
  • Handling Dirty Data
  • Finalising Data
  • Making the Package Dynamic

Scripting In SSIS

  • Using Visual Studio Tools for Applications (VSTA) Scripting IDE
  • Using the Script Task
  • Using the Script Component
  • Accessing Variables
  • Connecting to Data Sources
  • Raising Events
  • Logging

Advanced Data Cleansing

  • Advanced Derived Column
  • Advanced Fuzzy Lookup and Fuzzy Grouping
  • DQS Cleansing
  • Master Data Management

Reliability and Scalability

  • Restarting Packages: Control flow, containers, checkpoints
  • Package Transactions: single transaction, multiple transactions, multiple packages
  • Error Outputs:
  • Scaling Out: Memory, Staging Data, Parallel Loading

Error and Event Handling

  • Precedence Constraints
  • Event Handling
  • Breakpoints
  • Error Rows
  • Logging

Overview of Programming and Extending SSIS

  • Creating Data Flow Components: Source, Transformation, Destination
  • Creating New Tools for Toolboxes

Administering SSIS

  • Using the SSIS Catalog
  • Deployment Models
  • Using TSQL with SSIS: Using parameters, environments and data taps
  • Creating a Central SSIS Server
  • Clustering SSIS
  • Package Configuration
  • Command-Line Utilities: DTExec, DTExecUI, DTUtil
  • Security
  • Scheduling Packages: SQL Server Agent and Proxy Accounts
  • 64 bit Issues
  • Monitoring Package Execution
  • Performance Counters

Course Dates

London CentralLondon GreenwichBristol

Dates Price Book Now
11 - 13 Dec £1195 + VAT Book Now
2018
8 - 10 Jan £1195 + VAT Book Now
5 - 7 Feb £1195 + VAT Book Now
5 - 7 Mar £1195 + VAT Book Now
See more dates...
All our courses run in London and Bristol, or can be tailored to requirements at your own offices. For alternative dates, please email or call us on 0208 269 4260
All prices are per person and are subject to VAT
15% discount for groups of four delegates.
10% discount for groups of three delegates.
(This offer applies for delegates on the same public scheduled course on the same date.)


"Our trainer was very approachable and knowledgeable."

Bond Pearce

"The course was well structured, clear and in a comfortable setting. The tutoring was exceptional."

Dot2Dot

"Very helpful, knowledgeable and responsive trainer."

Westminster University



© Transmedia . 85 Tottenham Court Road, London, W1T 4TQ  UK . 0208 269 4260