8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
1/23
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.
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
2/23
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
3/23
Typical use of IntegrationTypical use of Integration
ServicesServices Merging Data from Heterogeneous Data StoresMerging Data from Heterogeneous Data Stores
Populating Data Warehouses a
nd Data
MartsPopulati
ng Data Warehouses a
nd Data
Marts
Cleaning and Standardizing DataCleaning and Standardizing Data
Building Business Intelligence into a Data TransformationBuilding Business Intelligence into a Data TransformationProcessProcess
Automating Administrative Functions and Data LoadingAutomating Administrative Functions and Data Loading
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
4/23
Data integration withoutData integration without
SSISSSIS
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
5/23
Data Integration withData Integration with
SSISSSIS
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
6/23
SSIS ArchitectureSSIS Architecture
Integration Services has a completely newIntegration Services has a completely newarchitecture that separates data movement andarchitecture that separates data movement andtransformation from Package control flow andtransformation from Package control flow and
managementmanagement
Packages are the units of work that you executePackages are the units of work that you execute
Tasks do the work in packagesTasks do the work in packages
Containers are objects that provide structure toContainers are objects that provide structure topackagespackages
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
7/23
-The run-time engine implementsthe control flow and packagemanagement infrastructure
-The data flow engineis a specialized, high performanceengine that is exclusivelydedicated to extracting,transforming, and loading data.
SSISSSIS
ArchitectureArchitecture
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
8/23
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 mainbuilt into the BI Development Studio and is the main
surface for package development.surface for package development.
CommandCommand--prompt utilitiesprompt utilities Dtexec (run an existing package at the command prompt )Dtexec (run an existing package at the command prompt )
Dtutil (manage existing packages at the commandDtutil (manage existing packages at the commandprompt)prompt)
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
9/23
SSIS DevelopmentSSIS Development
EnvironmentEnvironment SSIS Projects:SSIS Projects:
Data SourcesData Sources
Data Source ViewsData Source Views SSIS Packages:SSIS Packages:
Control flowControl flow TasksTasks Loops, seque nces 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
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
10/23
Using SQL ServerUsing SQL Server
Integration ServicesIntegration Services
What Are Data Sources and DataWhat Are Data Sources and DataSource Views?Source Views?
WhatIs a ConnectionManager?WhatIs a ConnectionManager?
WhatIs Package Control Flow?WhatIs Package Control Flow?
WhatIs a Data Flow Task?WhatIs a Data Flow Task?
How to Deploy PackagesHow to Deploy Packages
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
11/23
Data Sources and DataData Sources and Data
Source ViewsSource Views Data sourcesData sources
A data source is a connection reference that you createA data source is a connection reference that you createoutside a package.outside a package.
A realA real--time reference to a data store connection, whichtime reference to a data store connection, whichincludes all tables and views in the data store.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 calculatedYou can extend a data source view by adding calculatedcolumns that are populated by custom expressions, addingcolumns that are populated by custom expressions, addingnew relationships between tables, replacing tables in thenew relationships between tables, replacing tables in thedata source view with queries, and adding related tables.data source view with queries, and adding related tables.
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
12/23
Connection ManagerConnection Manager
A connection manager is a logicalA connection manager is a logicalrepresentation of a connectionrepresentation of a connection
Different types of connectionDifferent types of connectionmanagers enable packagesmanagers enable packages
to connect to a varietyto connect to a varietyof data sources andof data sources and
serversservers
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
13/23
Package Control FlowPackage Control Flow
TasksTasks Tasks do the work in packages. The mostTasks do the work in packages. The most
important task is theimporta
nt 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 loopContainers allow you to group together, or loop
through, the tasksthrough, the tasks
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
14/23
Deploy PackageDeploy Package
Define package configurationsDefine package configurations Allow you to update the values of properties at run timeAllow you to update the values of properties at run time
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
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
15/23
SSIS Execution MethodsSSIS Execution Methods
Visual Studio / BIDSVisual Studio / BIDS
Debugging environmentDebugging environment
Command LineCommand Line (dtexec)(dtexec)
Execute Package Utility (dtexecui)Execute Package Utility (dtexecui)
SQL Agent Sub SystemSQL Agent Sub System
ObjectModelObjectModel
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
16/23
SSIS DesignerSSIS Designer
Available in Business Intelligence DevelopmentAvailable in Business Intelligence DevelopmentStudio as part of anIntegration Services project.Studio as part of anIntegration Services 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 objects.Adding event handlers to the package and package objects.
Viewing the package content.Viewing the package content.
At run time, viewing the execution progress of the package.At run time, viewing the execution progress of the package.
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
17/23
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
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
18/23
SecuritySecurity
Packages signed with certificatesPackages signed with certificates
Data encryption before saving the packageData encryption before saving the package
Packages protected using passwordsPackages protected using passwords
Packages controlled with databasePackages controlled with database--levellevelroles, by default:roles, by default:
AdministratorAdmi
nistrator
UserUser
OperatorOperator
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
19/23
CompatibilityCompatibility
Possibility to run DTS and SSISPossibility to run DTS and SSISpackagespackages
Use Execute DTS 2000 Package Task forUse Execute DTS 2000 Package Task forrunning DTS packages from Integrationrunning DTS packages from IntegrationServices PackagesServices Packages
Use Package Migration Wizard forUse Package Migration Wizard formigrating packages with SQLServermigrating packages with SQLServer2000 DTS format2000 DTS format
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
20/23
Migration limitationsMigration limitations
Doesnt migrate ActiveX Script code with thatDoesnt migrate ActiveX Script code with thataccesses the DTS object model via parent propertyaccesses the DTS object model via parent propertyof the Global Variablesof the Global Variables
Doesnt migrate Analysis Services tasksDoesnt migrate Analysis Services tasksDoesnt migrate Complex Data transformation tasksDoesnt migrate Complex Data transformation tasksDoesnt migrate customs tasksDoesnt migrate customs tasksDoesnt migrate dynamic propertiesDoesnt migrate dynamic propertiesDoesnt migrate parallel data pumpsDoesnt migrate parallel data pumps
Doesnt migrate ActiveX Script attached to packageDoesnt migrate ActiveX Script attached to packagestepsstepsDoesnt migrate transaction settingsDoesnt migrate transaction settings
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
21/23
BreakBreak
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
22/23
DemoDemo
8/6/2019 SQL Server 2005 -SSIS Tarek Ghazali
23/23
Resources & QuestionsResources & Questions
Contact meContact me :: [email protected]@sqlmvp.com www.sqlmvp.comwww.sqlmvp.com
Microsoft Resources:Microsoft Resources: msdn.microsoft.com/sqlserver/msdn.microsoft.com/sqlserver/ www.microsoft.com/sql/communitywww.microsoft.com/sql/community
Download Presentations:Download Presentations: www.lebdev.netwww.lebdev.net www.devconnect.netwww.devconnect.net
Site dedicated to Integration Services:Site dedicated to Integration Services: www.SQLIS.comwww.SQLIS.com
Top Related