SQL Server Integration Services (SSIS)

Presented by Tarek GhazaliIT Technical SpecialistIT Technical SpecialistMicrosoft SQL Server (MVP)Microsoft Certified Technology Specialist (MCTS)Microsoft Certified Technology Specialist (MCTS)Web Development (MCP)Web Development (MCP)LebDev Vice President

© 2007 Tarek Ghazali. All rights reserved.

What Is SSIS?What Is SSIS?

Integration Services is a platform for building high Integration Services is a platform for building high performance data integration and workflow solutions, performance data integration and workflow solutions, including extraction, transformation, and loading (ETL) including extraction, transformation, and loading (ETL) operations for data warehousing.operations for data warehousing.

Transfer and transform dataTransfer and transform data– SSIS contains a data-flow engine to transfer and SSIS contains a data-flow engine to transfer and

transform data to and from varied data sourcestransform data to and from varied data sources

Tools and wizardsTools and wizards– SSIS contains graphical tools and wizards for creating an SSIS contains graphical tools and wizards for creating an

extraction, transform, and loading systemextraction, transform, and loading system

New version of DTS (Data Transformation Services in New version of DTS (Data Transformation Services in SQL Server 2000)SQL Server 2000)

Typical use of Typical use of Integration ServicesIntegration Services

Merging Data from Heterogeneous Data StoresMerging Data from Heterogeneous Data Stores

Populating Data Warehouses and Data Marts Populating Data Warehouses and Data Marts

Cleaning and Standardizing Data Cleaning and Standardizing Data

Building Business Intelligence into a Data Building Business Intelligence into a Data Transformation Process Transformation Process

Automating Administrative Functions and Data Automating Administrative Functions and Data Loading Loading

Data integration Data integration without SSISwithout SSIS

Data Integration with Data Integration with SSISSSIS

SSIS ArchitectureSSIS Architecture

– Integration Services has a completely new Integration Services has a completely new architecture that separates data architecture that separates data movement and transformation from movement and transformation from Package control flow and managementPackage control flow and management

– Packages are the units of work that you Packages are the units of work that you executeexecute

– Tasks do the work in packagesTasks do the work in packages

– Containers are objects that provide Containers are objects that provide structure to packagesstructure to packages

-The run-time engine implements the control flow and package management infrastructure

-The data flow engine is a specialized, high performance engine that is exclusively dedicated to extracting, transforming, and loading data.

SSIS SSIS ArchitectuArchitecturere

SSIS ToolsSSIS Tools

WizardsWizards– SQL Server Import and Export WizardSQL Server Import and Export Wizard– Package Migration WizardPackage Migration Wizard– ……

SSIS DesignerSSIS Designer– built into the BI Development Studio and is the built into the BI Development Studio and is the

main surface for package development. main surface for package development. Command-prompt utilitiesCommand-prompt utilities

– Dtexec (run an existing package at the command Dtexec (run an existing package at the command prompt )prompt )

– Dtutil (manage existing packages at the command Dtutil (manage existing packages at the command prompt) prompt)

SSIS Development SSIS Development EnvironmentEnvironment

SSIS Projects:SSIS Projects:

– Data SourcesData Sources– Data Source ViewsData Source Views– SSIS Packages:SSIS Packages:

Control flowControl flow– TasksTasks– Loops, sequences and eventsLoops, sequences and events– Variables and scopingVariables and scoping– Precedence constraintsPrecedence constraints

Connections ManagerConnections Manager

Data flowData flow– Source and destination adaptersSource and destination adapters– TransformationsTransformations– Multiple sources with joins and unionsMultiple sources with joins and unions– Multiple destinations with splits and multicastMultiple destinations with splits and multicast

Using SQL Server Using SQL Server Integration ServicesIntegration Services

What Are Data Sources and Data What Are Data Sources and Data Source Views?Source Views?

What Is a Connection Manager?What Is a Connection Manager? What Is Package Control Flow?What Is Package Control Flow? What Is a Data Flow Task?What Is a Data Flow Task? How to Deploy PackagesHow to Deploy Packages

Data Sources and Data Data Sources and Data Source ViewsSource Views Data sourcesData sources

– A data source is a connection reference that you A data source is a connection reference that you create outside a package. create outside a package.

– A real-time reference to a data store connection, A real-time reference to a data store connection, which includes all tables and views in the data store. which includes all tables and views in the data store.

Data source viewsData source views– Provide a subset of data from a data sourceProvide a subset of data from a data source – You can extend a data source view by adding You can extend a data source view by adding

calculated columns that are populated by custom calculated columns that are populated by custom expressions, adding new relationships between expressions, adding new relationships between tables, replacing tables in the data source view with tables, replacing tables in the data source view with queries, and adding related tables. queries, and adding related tables.

Connection ManagerConnection Manager

A connection manager is a logical A connection manager is a logical representation of a connectionrepresentation of a connection

Different types of connection Different types of connection managers enable packagesmanagers enable packages

to connect to a variety to connect to a variety of data sources and of data sources and serversservers

Package Control FlowPackage Control Flow

TasksTasks– Tasks do the work in packages. The most Tasks do the work in packages. The most

important task is the important task is the data flow taskdata flow task

Precedence constraintsPrecedence constraints– Precedence constraints join tasks togetherPrecedence constraints join tasks together

ContainersContainers– Containers allow you to group together, or Containers allow you to group together, or

loop through, the tasksloop through, the tasks

Deploy PackageDeploy Package

Define package configurationsDefine package configurations– Allow you to update the values of properties at run Allow you to update the values of properties at run

timetime

Build the projectBuild the project– Configure the deployment utilityConfigure the deployment utility– Build the projectBuild the project

Deploy the buildDeploy the build– Copy the build folder to the new systemCopy the build folder to the new system– Run the manifestRun the manifest

SSIS Execution SSIS Execution MethodsMethods Visual Studio / BIDSVisual Studio / BIDS

– Debugging environmentDebugging environment Command Line – (dtexec)Command Line – (dtexec) Execute Package Utility (dtexecui)Execute Package Utility (dtexecui) SQL Agent Sub SystemSQL Agent Sub System Object ModelObject Model

SSIS DesignerSSIS Designer

Available in Business Intelligence Development Available in Business Intelligence Development Studio as part of an Integration Services Studio as part of an Integration Services project. project.

Perform the following tasks:Perform the following tasks:– Constructing the control flow in a package.Constructing the control flow in a package.

– Constructing the data flows in a package.Constructing the data flows in a package.

– Adding event handlers to the package and package Adding event handlers to the package and package objects.objects.

– Viewing the package content.Viewing the package content.

– At run time, viewing the execution progress of the At run time, viewing the execution progress of the package.package.

Packages DebuggingPackages Debugging

Control flowControl flow– BreakpointsBreakpoints– Progress reportingProgress reporting– Debug windowDebug window

Data flowData flow– Data viewerData viewer– Row countsRow counts– Progress reportingProgress reporting

SecuritySecurity

Packages signed with certificatesPackages signed with certificates Data encryption before saving the Data encryption before saving the

packagepackage Packages protected using passwordsPackages protected using passwords Packages controlled with database-Packages controlled with database-

level roles, by default:level roles, by default:– AdministratorAdministrator– UserUser– OperatorOperator

CompatibilityCompatibility

Possibility to run DTS and SSIS Possibility to run DTS and SSIS packagespackages– Use ‘Execute DTS 2000 Package Use ‘Execute DTS 2000 Package

Task’ for running DTS packages from Task’ for running DTS packages from Integration Services PackagesIntegration Services Packages

Use ‘Package Migration Wizard’ Use ‘Package Migration Wizard’ for migrating packages with for migrating packages with SQLServer 2000 DTS formatSQLServer 2000 DTS format

Migration limitationsMigration limitations

Doesn’t migrate ActiveX Script code with that Doesn’t migrate ActiveX Script code with that accesses the DTS object model via parent accesses the DTS object model via parent property of the Global Variablesproperty of the Global VariablesDoesn’t migrate Analysis Services tasksDoesn’t migrate Analysis Services tasksDoesn’t migrate Complex Data transformation Doesn’t migrate Complex Data transformation taskstasksDoesn’t migrate customs tasksDoesn’t migrate customs tasksDoesn’t migrate dynamic propertiesDoesn’t migrate dynamic propertiesDoesn’t migrate parallel data pumpsDoesn’t migrate parallel data pumpsDoesn’t migrate ActiveX Script attached to Doesn’t migrate ActiveX Script attached to package stepspackage stepsDoesn’t migrate transaction settingsDoesn’t migrate transaction settings

BreakBreak

DemoDemo

Resources & QuestionsResources & Questions

Contact me Contact me : : – tghazali@sqlmvp.comtghazali@sqlmvp.com– www.sqlmvp.comwww.sqlmvp.com

Microsoft Resources:Microsoft Resources:– msdn.microsoft.com/sqlservermsdn.microsoft.com/sqlserver//– www.microsoft.com/www.microsoft.com/sqlsql/community/community

Download Presentations:Download Presentations:– www.lebdev.netwww.lebdev.net– www.devconnect.netwww.devconnect.net

Site dedicated to Integration Site dedicated to Integration Services:Services:– www.SQLIS.comwww.SQLIS.com