SQL Server 2005 Integration Services Deep Dive
Synopsis
This 4-Day course is designed for those people who want to know what SQL Server
Integration Services (SSIS) can do in their workplace. The course does not
spend time on the basics of the product rather it gets right into the details and
digs away at what really makes it an enterprise class ETL tool. Students on
the course will see demonstrations on how to implement advanced features in SSIS
as well as get the opportunity to do practical labs around what they have learnt.
The labs are designed so that students have to think about the solution rather than
follow a "dot-to-dot" description of what to do. This is much more akin to
what will happen in the real world, albeit with your expert instructor ready to
offer detailed explanations and guidance as required.
Course Objectives
The course is designed for people who have already created SSIS solutions and want
to take their knowledge level to the next stage. After attending the course,
students will have a detailed appreciation of the more complex aspects of SSIS and
also gain an insight into what SSIS is doing underneath the covers allowing them
to make more informed decisions about all aspects of SSIS package and process design
going forward.
Intended Audience
The intended audience are those who have used SSIS before and who are comfortable
navigating their way around the product. The course will best suit those people
who want to take their SSIS product knowledge to the next level and have a desire
to understand the reasons why different design decisions are made.
Prerequisites
A good understanding of basic SSIS functionality for both Control Flow tasks and
Data Flow.
A basic understanding of data warehouse concepts and terms is desirable.
Outline Course Contents
The Essentials
It is essential that to be able to use SSIS properly we have an appreciation of
some of the major composite parts. The first part of this course looks to
give us that knowledge by examining in detail the two following product sections,
the Control Flow and the Data Flow
Control Flow
- Principal Work Areas
- The Toolbox
- Variables
- Looping
- Event Handlers
- Logging
- Property Expressions
- Configurations
- Expression Language
- Restarts
|
Data Flow
- What is it
- The Toolbox
- Sources & Destinations
- Paths
- Data Viewers
- Transformations
- Types of Transformation
- Asynchronous and Synchronous
- Pipeline Internals (Data Flow Engine)
- LineageIDs
- Buffer Profiles
- Execution plans and trees
- Threading
|
Data Warehouse ETL
A robust and functional ETL process is key to any data warehouse implementation
and SSIS is an excellent choice of tool for that. This module builds on the previous
knowledge gained and applies it in the form of solutions to the common scenarios
encountered during a data warehousing ETL process.
Terminology & Uses
Dimension Tables
- Definition
- Dimension Change Types
- Inferred Members
- The SCD Transformation
- Building your own
|
Fact Tables
- Definition
- Grain Considerations
- Late Arriving Facts
- Fact Table Loading
- Loading directly to SSAS
|
Transactions
- Terminology and uses
- Transactions
- Configuration
- Using the Distributed Transaction Coordinator
- Using Native Transactions
|
Fuzzy Logic
- What is it
- How does it work
- Why would we use it
- When and where do we use it
|
Scripting
- Tasks versus Components
- What you can and cannot do with them
- When to use them and when not to
- Things to consider
- Examples of usage
|
Deployment & Execution
Methods of Package Deployment
- Copy and Paste
- Deployment utility
- DTUtil
|
Methods of Package Execution
- Business Intelligence Development Studio (BIDS)
- DTExec
- DTExecUI
- SQL Server Agent
|
Integration Services in SQL Server 2008
Introduction to the new features in SQL Server 2008 Integration Services.
We are currently delivering this course on request, please contact us for more details.