Introduction to SQL Server 2008 Integration Services

view of Microsoft Corporation on the issues discussed as of

the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This slide deck is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this slide deck may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this slide deck. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this slide deck does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.

© 2008 Microsoft Corporation. All rights reserved.
Microsoft, SQL Server, Office System, Visual Studio, SharePoint Server, Office PerformancePoint Server, .NET Framework, ProClarity Desktop Professional are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

title goes in here

Transformation

Tables
Populating Fact Tables

actionable information
Challenges
Retrieve and merge data from multiple sources
Cleanse and transform

the data
Load the data into appropriate data stores for analysis and reporting
Enterprises spend 60%–80% of their resources in the data integration stage

heterogeneous data stores
Automating administrative tasks

and general-purpose data integration
Provides a robust, flexible, fast, scalable, and

extensible architecture
Challenges traditional ETL design approaches

dimension key
Transform

flow
Data flow
32-bit and 64-bit editions

collection of:
Connection managers
Control flow components
Data flow components
Variables
Event handlers
Configurations
Can be designed

graphically or built programmatically
Saved in XML format to the file system or SQL Server

cannot be shared between packages
Used by package elements
Does not need

to connect to SQL Server

a single control flow
Control flow elements
Containers
Tasks
Precedence constraints
Variables

defined by precedence constraints in control flow
Manage variable and transactional

boundaries

precompiled for better performance
Develop VB.NET or C# .NET scripts in

Visual Studio for Applications (VSTA) using these features
IntelliSense
Color coding
Integrated Help
References:
.NET Framework libraries
Managed assemblies
COM objects
Debugging
Consider a Custom Task to reuse functionality between packages

of execution
Configure conditions that determine whether the executable runs
Success, Failure,

or Completion Constraints
Expressions
Logical AND/OR for multiple constraints

properties
System variables store values collected while the package runs
User variables

store values assigned
By default in the current package
By a variable passed by a parent package
By an expression
All variables use case-sensitive names
Variables can be scoped at package, container, or task level

the context of an overall control flow
Performs traditional ETL in

addition to other extended scenarios
Is fast and scalable
Data Flow Components
Extract data from Sources
Load data into Destinations
Modify data with Transformations
Service Paths
Connect data flow components
Create the pipeline

databases

databases and objects
DataReaders and Recordsets

in the pipeline input

or unpivoted rowsets
Are also called asynchronous components
Mar-2008
Microsoft Developer & Platform

Evangelism

the transformation inputs
Join multiple inputs into one output
Perform lookup operations

single dimension or fact table
A “master package” orchestrates the sequence

of the entire ETL process by using Execute Package tasks
Execute all dimension packages first
Execute all fact table packages second
A robust design includes extensive logging to store execution history and audit trails
Implement package logging to capture most execution details
Customize and extend logging by using control flow tasks

packages benefit from this transformation

key
Transform
The Lookup transformation is ideal for dimension key lookups and

can be configured to manage lookup failures

0 (Fixed Attribute)
Type 1 (Changing Attribute)
Type 2 (Historical Attribute)
Inferred member

management
Automatically constructs the downstream data flow
Handles the majority of slowly changing dimension scenarios

not preserved

is preserved
Most common form of Slowly Changing Dimension
SalesTerritoryKey update to

10

U.S. ISV training. We’re dedicated to providing comprehensive training for

the entire developer community so be sure to make www.isvinnovation.com your first stop for all your training needs.
This site is filled with fantastic new resources, features, and functionality. We plan to make this site a one-stop shop for all your ISV needs so if you have any feedback please contact us as usisv@microsoft.com.