sql server 2005 integration services n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2005 Integration Services PowerPoint Presentation
Download Presentation
SQL Server 2005 Integration Services

Loading in 2 Seconds...

play fullscreen
1 / 21

SQL Server 2005 Integration Services - PowerPoint PPT Presentation


  • 80 Views
  • Uploaded on

SQL Server 2005 Integration Services. Matthew Stephen IT Pro Evangelist (SQL Server) http://blogs.technet.com/mat_stephen Microsoft Ltd. What is SQL Server Integration Services?. A new Microsoft SQL Server Business Intelligence application The successor to Data Transformation Services

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server 2005 Integration Services' - nasnan


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql server 2005 integration services

SQL Server 2005 Integration Services

Matthew Stephen

IT Pro Evangelist (SQL Server)

http://blogs.technet.com/mat_stephen

Microsoft Ltd.

what is sql server integration services
What is SQL Server Integration Services?
  • A new Microsoft SQL Server Business Intelligence application
  • The successor to Data Transformation Services
  • The platform for a new generation of high performance data integration technologies
sql server business intelligence

Integrate

Analyze

Report

SQL Server Business Intelligence
  • Data enrichment, with business logic, hierarchical views
  • Data discovery via data mining
  • Data acquisition from source systems and integration
  • Data transformation and synthesis
  • Data presentation and distribution
  • Data access for the masses
example before integration services

Alerts & escalation

Call centre data: semi structured

Data mining

ETL

Text Mining

Staging

Legacy data: binary files

Staging

Warehouse

ETL

Hand

coding

Staging

Cleansing

&

ETL

Reports

Application database

ETL

Mobile

data

Example: before Integration Services
  • Integration and warehousing require separate, staged, operations.
  • Preparation of data requires different, often incompatible, tools.
  • Reporting and escalation is a slow process, delaying smart responses.
  • Heavy data volumes make this scenario increasingly unworkable.
example with integration services

Alerts & escalation

Mobile

data

Text mining

components

Call centre:

semi-structured data

Merges

Data cleansing

components

Data mining

components

Standard

sources

Custom

source

Warehouse

Legacy data: binary files

Reports

SQL Server Integration Services

Application database

Example: with Integration Services
  • Integration and warehousing are a seamless, manageable, operation.
  • Sourced, prepare and load data in a single, auditable process.
  • Reporting and escalation can be parallelized with the warehouse load.
  • Scales to handle heavy and complex data requirements.
how ssis operates
How SSIS Operates
  • Data sources can be diverse, including custom or scripted adapters
  • Transformation components shape and modify data in many ways.
  • Data is routed by rules or error conditions for cleansing and conforming.
  • Flows can be as complex as your business rules, but highly concurrent.
  • And finally data can be loaded in parallel to many varied destinations.
customer benefits of ssis
Customer benefits of SSIS
  • Performance
    • Data flows process large volumes of data efficiently
  • Facility
    • Many prebuilt adapters and transformations reduce hand coding
    • Extensible object model
    • Highly productive visual environment
    • Data cleansing features
    • Data mining
      • imputation of incomplete data
feature drilldown data integration
Feature drilldown:Data Integration
  • Traditional data sources
  • XML
  • Custom data sources
  • Integrate diverse sources
  • Parallel loading of diverse destinations
feature drilldown data warehousing
Feature drilldown:Data Warehousing
  • Sorting and aggregation during loading
  • Multicast and partition
  • Slowly changing dimensions
  • Load and process Analysis Services cubes
feature drilldown intelligent data handling
Feature drilldown:Intelligent Data Handling
  • Capture error rows
  • Fuzzy lookup and grouping
  • Data mining on the data flow
  • Text mining
feature drilldown large data volumes
Feature drilldown:Large Data Volumes
  • Efficient data sources
  • High performance processing
    • > 700% faster than DTS 2000
  • Unique SQL Server Destination
    • > 8% faster than Bulk Insert
  • Advanced data flow architecture
    • Enables flexible concurrent processing
feature drilldown development
Feature drilldown:Development
  • Visual studio integration
  • Visual designer
  • Visual debugging
  • Build and deploy
  • Custom code integration
enabling new architectures
Enabling new architectures …

Traditional (DTS) warehouse loading

  • Integration process simply conforms data and loads the database server
  • The database performs aggregations, sorting and other operations
  • Database competes for resources from user queries
  • This solution does not scale very well
enabling new architectures1
Enabling new architectures …

Warehouse loading with SQL Server Integration Services

  • SQL Server Integration Services conforms the data
  • But also aggregates and sorts, and loads the database
  • This frees-up the database server for user queries
life cycle tools
Life Cycle tools
  • Design
    • Business Intelligence Designer
    • Migration wizard for pre SQL 2005 packages
    • Execute DTS 2000 package Task
    • Visual Source Safe Integration
  • Deployment
    • Deployment Utility
    • Command Line execution
    • Flexible Configuration Options
  • Supportability
    • Rich per package Logging
    • SSIS service
    • SQL Management Studio.
    • Checkpoint - Restart ability
sample server layout

Packages on file system

Destination data

Source data

SSIS package error rows

SSIS package Logging

SSIS packages stored in SQL

Sample Server Layout

Integration ServicesPackage Execution

Source Flat Files

SSIS support Server(s)

sample server layout1

Destination data

Source data

SSIS packages in SQL

SSIS support Server(s)

Logging

Error Rows

Sample Server Layout

SSIS ParentPackage Execution via SQL Agent (scheduled)

Parent calls children via SQL Agent on other machines

summary
Summary
  • SQL Server Integration Services is an exceptionally high performance integration and transformation tool
  • Some processes benefit more from parallelism, some from memory
  • Many new tasks and transforms
  • Separation of control flow and data flow
resources
Resources
  • Microsoft SQL Team blogshttp://www.sqljunkies.com/blogs
  • Microsoft SQL Server community on the webwww.sqlservercentral.com
  • SQL Server Integration Services on the webwww.sqlis.com
  • SQL Server Developer Centerhttp://msdn.microsoft.com/sql/
  • Microsoft SQL Server 2005 websitehttp://www.microsoft.com/sql/2005/default.asp
  • SQL Newsgroups http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
  • SQL Server 2005 Datamininghttp://www.sqlserverdatamining.com/DMCommunity/