SQL Server 2005 Integration Services Matthew Stephen IT Pro Evangelist

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 The platform for a new generation of high performance data integration technologies

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

Example: before Integration Services Alerts & escalation Call centre data: semi structured Text Mining Staging Staging Legacy data: binary files Hand coding Cleansing & ETL Application database ETL Data mining ETL Staging ETL Warehouse Reports Mobile data 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 Text mining components Data mining components Custom source Merges Standard sources Data cleansing components Mobile data Call centre: semi-structured data Warehouse Legacy data: binary files Application database SQL Server Integration Services Reports 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 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 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 Traditional data sources XML Custom data sources Integrate diverse sources Parallel loading of diverse destinations

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 Capture error rows Fuzzy lookup and grouping Data mining on the data flow Text mining

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 Visual studio integration Visual designer Visual debugging Build and deploy Custom code integration

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 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 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 Source data SSIS packages stored in SQL Integration Services Package Execution Source Flat Files SSIS package Logging SSIS package error rows SSIS support Server(s) Destination data

Sample Server Layout SSIS Parent Package Execution via SQL Agent (scheduled) SSIS packages in SQL Parent calls children via SQL Agent on other machines Source data Destination data Logging Error Rows SSIS support Server(s)

Demo

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 Microsoft SQL Team blogs http://www.sqljunkies.com/blogs Microsoft SQL Server community on the web www.sqlservercentral.com SQL Server Integration Services on the web www.sqlis.com SQL Server Developer Center http://msdn.microsoft.com/sql/ Microsoft SQL Server 2005 website http://www.microsoft.com/sql/2005/default.asp SQL Newsgroups http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx SQL Server 2005 Datamining http://www.sqlserverdatamining.com/DMCommunity/

2003 Microsoft Corporation. All rights reserved.