SSIS (463)

Our SSIS Training Course is the last modules covered for your MCSA Certification . Our course is very practical and our classes are small . We use SQL Server 2014 course material for this course.

R9,995.00

Description

SSIS Training Course

Our SSIS training course is the last module of your MCSA Certification. Our course is very practical and our classes are limited to small groups.

Alignment

Aligned with Microsoft’s 463 : Implementing a Data Warehouse with MS SQL

Prerequisites

Beginning SQL

Course Material

Included in the course price. We use course material based on SQL Server 2014.

Course Contents

DAY 1:

SQL Server SSIS Historical Overview

What’s New in SSIS
Tools of the Trade
Import and Export Wizard
The SQL Server Data Tools Experience
SSIS Architecture
Packages
Control Flow
Data Flow
Variables
Parameters
Error Handling and Logging
Editions of SQL Server

The SSIS Tools

Import and Export Wizard
SQL Server Data Tools
The Solution Explorer Window
The SSIS Toolbox
The Properties Windows
The SSIS Package Designer
Control Flow
Connection Managers
Variables
Data Flow
Parameters
Event Handlers
Package Explorer
Executing a Package
Management Studio

SSIS Tasks

SSIS Task Objects
Using the Task Editor
The Task Editor Expressions Tab
Looping and Sequence Tasks
Script Task (.NET)
Analysis Services Tasks
Analysis Services Execute DDL Task
Analysis Services Processing Task
Data Mining Query Task
Data Flow Task
Data Preparation Tasks
Data Profiler
File System Task
Archiving a File
FTP Task
Getting a File Using FTP
Web Service Task
Retrieving Data Using the Web Service Task
and XML Source Component
XML Task
Validating an XML File
RDBMS Server Tasks
Bulk Insert Task
Using the Bulk Insert Task
Execute SQL Task
Workflow Tasks
Execute Package Task
Execute Process Task
Message Queue Task
Send Mail Task
WMI Data Reader Task
WMI Event Watcher Task
Polling a Directory for the Delivery of a File
SMO Administration Tasks
Transfer Database Task
Transfer Error Messages Task
Transfer Logins Task
Transfer Master Stored Procedures Task
Transfer Jobs Task
Transfer SQL Server Objects Task

The Data Flow

Understanding the Data Flow
Data Viewers
Sources
OLE DB Source
Excel Source
Flat File Source
Raw File Source
XML Source
ADO.NET Source
Destinations
Excel Destination
Flat File Destination
OLE DB Destination
Raw File Destination
Recordset Destination
Data Mining Model Training
DataReader Destination
Dimension and Partition Processing
Common Transformations
Synchronous versus Asynchronous Transformations
Aggregate
Conditional Split
Data Conversion
Derived Column
Lookup
Row Count
Script Component
Slowly Changing Dimension
Sort
Union All
Other Transformations
Audit
Character Map
Copy Column
Data Mining Query
DQS Cleansing
Export Column
Fuzzy Lookup
Fuzzy Grouping
Import Column
Merge
Merge Join
Multicast
OLE DB Command
Percentage and Row Sampling
Pivot Transform
Unpivot
Term Extraction
Term Lookup
Data Flow Example

Using Variables, Parameters, and Expressions

Dynamic Package Objects
Variable Overview
Parameter Overview
Expression Overview
Understanding Data Types
SSIS Data Types
Date and Time Type Support
How Wrong Data Types and Sizes Can Affect Performance
Unicode and Non-Unicode Conversion Issues
Casting in SSIS Expressions
Using Variables and Parameters
Defining Variables
Defining Parameters
Variable and Parameter Data Types
Working with Expressions
C#-Like? Close, but Not Completely
The Expression Builder
Syntax Basics
Using Expressions in SSIS Packages

DAY 2:

Containers

Task Host Containers
Sequence Containers
Groups
For Loop Container
Foreach Loop Container
Foreach File Enumerator Example
Foreach ADO Enumerator Example

Joining Data

The Lookup Transformation
Using the Merge Join Transformation
Contrasting SSIS and the Relational Join
Lookup Features
Building the Basic Package
Using a Relational Join in the Source
Using the Merge Join Transformation
Using the Lookup Transformation
Full-Cache Mode
No-Cache Mode
Partial-Cache Mode
Multiple Outputs
Expressionable Properties
Cascaded Lookup Operations
Cache Connection Manager and Cache Transform

Creating an End-to-End Package

Basic Transformation Tutorial
Creating Connections
Creating the Control Flow
Creating the Data Flow
Completing the Package
Saving the Package
Executing the Package
Typical Mainframe ETL with Data Scrubbing
Creating the Data Flow
Handling Dirty Data
Finalizing
Handling More Bad Data
Looping and the Dynamic Tasks
Looping
Making the Package Dynamic

Scripting in SSIS

Introducing SSIS Scripting
Getting Started in SSIS Scripting
Selecting the Scripting Language
Using the VSTA Scripting IDE
Example: Hello World
Adding Code and Classes
Using Managed Assemblies
Example: Using Custom .NET Assemblies
Using the Script Task
Configuring the Script Task Editor
The Script Task Dts Object
Accessing Variables in the Script Task
Connecting to Data Sources in a Script Task
Raising an Event in a Script Task
Writing a Log Entry in a Script Task
Using the Script Component
Differences from a Script Task
Configuring the Script Component Editor
Accessing Variables in a Script Component
Connecting to Data Sources in a Script Component
Raising Events
Logging
Example: Data Validation
Synchronous versus Asynchronous
Essential Coding, Debugging, and Troubleshooting
Techniques
Structured Exception Handling
Script Debugging and Troubleshooting

Advanced Data Cleansing in SSIS

Advanced Derived Column Use
Text Parsing Example
Advanced Fuzzy Lookup and Fuzzy Grouping
Fuzzy Lookup
Fuzzy Grouping
DQS Cleansing
Data Quality Services
DQS Cleansing Transformation
Master Data Management
Master Data Services

DAY 3:

Incremental Loads in SSIS

Control Table Pattern
Querying the Control Table
Querying the Source Table
Updating the Control Table
SQL Server Change Data Capture
Benefits of SQL Server CDC
Preparing CDC
Capture Instance Tables
The CDC API
Using the SSIS CDC Tools

Loading a Data Warehouse

Data Profiling 383
Initial Execution of the Data Profiling Task 383
Reviewing the Results of the Data Profiling Task 386
Turning Data Profile Results into Actionable ETL Steps 390
Data Extraction and Cleansing 391
Dimension Table Loading 391
Loading a Simple Dimension Table 392
Loading a Complex Dimension Table 397
Considerations and Alternatives to the
SCD Transformation 408
Fact Table Loading 409
SSAS Processing 421
Using a Master ETL Package 426

Using the Relational Engine

Data Extraction 430
SELECT * Is Bad 430
WHERE Is Your Friend 432
Transform during Extract 433
Many ANDs Make Light Work 437
SORT in the Database 437
Modularize 439
SQL Server Does Text Files Too 440
Using Set-Based Logic 444
Data Loading 446
Database Snapshots 446
The MERGE Operator 448

Accessing Heterogeneous Data

Excel and Access 455
64-Bit Support 455
Working with Excel Files 457
Working with Access 462
Importing from Oracle 469
Oracle Client Setup 469
Importing Oracle Data 470
Using XML and Web Services 472
Configuring the Web Service Task 472
Working with XML Data as a Source 483
Flat Files 486
Loading Flat Files 487
Extracting Data from Flat Files 489
ODBC 491
Other Heterogeneous Sources 494

DAY 4:

Reliability and Scalability

Restarting Packages 498
Simple Control Flow 499
Containers within Containers and Checkpoints 501
Variations on a Theme 503
Inside the Checkpoint File 505
Package Transactions 507
Single Package, Single Transaction 508
Single Package, Multiple Transactions 509
Two Packages, One Transaction 511
Single Package Using a Native Transaction in SQL Server
Error Outputs 513
Scaling Out 516
Architectural Features 516
Scaling Out Memory Pressures 517
Scaling Out by Staging Data 517
Scaling Out with Parallel Loading 522

Understanding and Tuning

the Data Flow Engine 529
The SSIS Engine 530
Understanding the SSIS Data Flow and Control Flow 530
Handling Workflows with the Control Flow 533
Data Processing in the Data Flow 533
Memory Buffer Architecture 534
Types of Transformations 534
Advanced Data Flow Execution Concepts 543
SSIS Data Flow Design and Tuning 549
Data Flow Design Practices 550
Optimizing Package Processing 555
Troubleshooting Data Flow Performance Bottlenecks 558
Pipeline Performance Monitoring 559

SSIS Software Development Life Cycle

Introduction to Software Development Life Cycles 565
SDLCs: A Brief History 566
Types of Software Development Life Cycles 566
Versioning and Source Code Control 567
Subversion (SVN) 568
Team Foundation Server, Team System, and SSIS 573
Summary 590
Chapter 18: Error and Event Handling 591
Using Precedence Constraints 592
Precedence Constraint Basics 592
Advanced Precedence Constraints and Expressions 593
Event Handling 601
Events 602
Using Event Handlers 603
Event Handler Inheritance 611
Breakpoints 612
Error Rows 616
Logging 622
Logging Providers 622
Log Events 623
Catalog Logging 627

DAY 5:

Programming and Extending

The Sample Components
Component 1: Source Adapter
Component 2: Transform
Component 3: Destination Adapter
The Pipeline Component Methods
Design-Time Functionality
Runtime
Connection Time
Building the Components
Preparation
Building the Source Component
Building the Transformation Component
Building the Destination Adapter
Using the Components
Installing the Components
Debugging Components
Design Time
Building the Complete Package
Runtime Debugging
Upgrading to SQL Server

Adding a User Interface to Your Component

Three Key Steps for Designing the UI: An Overview
Building the User Interface
Adding the Project
Implementing IDtsComponentUI
Setting the UITypeName
Building the Form
Extending the User Interface
Runtime Connections
Component Properties
Handling Errors and Warnings
Column Properties
Other UI Considerations

External Management and WMI Task

Implementation
External Management of SSIS with Managed Code
Setting Up a Test SSIS Package for Demonstration Purposes
The Managed Object Model Code Library
Catalog Management
Folder Management
Environments
The DTS Runtime Managed Code Library
SSIS Deployment Projects
Parameter Objects
Server Deployment
Executing SSIS Packages Deployed to the SSIS Catalog
Environment References
Package Operations
Application Object Maintenance Operations
Package Operations
Package Monitoring
Project, Folder, and Package Listing
A Package Management Example
Package Log Providers
Specifying Events to Log
Programming to Log Providers
SQL Server 2014 Operation Logs
Package Configurations
Creating a Configuration
Programming the Configuration Object
Configuration Object
Windows Management Instrumentation Tasks
WMI Reader Task Explained
WMI Data Reader Example
WMI Event Watcher Task
WMI Event Watcher Task Example

Administering SSIS

Using the SSIS Catalog

Setting the SSIS Catalog Properties
SSISDB
Deployment Models
Project Deployment Model
Package Deployment Model
Using T-SQL with SSIS
Executing Packages
Using Parameters
Querying Tables for Parameter Values
Using Environments
Using Data Taps
Creating a Central SSIS Server
Clustering SSIS
Package Configuration
Command-Line Utilities
DTExec
DTExecUI
DTUtil
Security
Securing the SSIS Catalog
Legacy Security
Scheduling Packages
SQL Server Agent
Proxy Accounts
64-Bit Issues
Monitoring Package Executions
Built-in Reporting
Custom Reporting
Performance Counters

Duration and pricing

  • Full-time over 5 days (R9995)
  • Part-time over 4 weeks (2 nights per week, 3 hour sessions) (R11995)
  • Part-time over 8 Saturdays, 3 hour sessions (R11995)
  • Please note : For part-time courses we do not have a fixed schedule and you will be placed on a waiting list until we get a group of 4+ together. Please book with no dates on the bookings form. This will automatically put you on the waiting list. We will confirm with you as soon as we have a part-time group together.
  • Distance-learning over up to 3 months (R9995)
  • International exams are not included in the course price.
  • Prices exclude Vat for Vat-registered companies

Certificate

  1. Upon completion of this course we will issue you with attendance certificate to certify your attendance and / or completion of the prescribed minimum examples.
  2. You will get an exam discount voucher plus guidance for exam preparation material.

Bookings

You can download the course registration form on our home page or by clicking here

Brochure

You may download a pdf copy of this page by clicking on the pdf icon at the top of the page.

Questions

Please email us

Schedule

On the calendar below. If your browser doesn’t display the calendar below, please click on this link or try using Google Chrome, alternatively please enquire via our Contact Us page.