Transcript Slide 1
SQL Server 2005 Integration Services Mike Taulty Developer & Platform Group Microsoft Ltd [email protected] http://mtaulty.com 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 SQL Server Integration Services Successor to Data Transformation Services New architecture and implementation Existing DTS packages can be run or migrated Design time experience in VS 2005 or Business Intelligence Development Studio Administration in SQL Server Management Studio Extensibility through .NET code 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. With Integration Services Alerts & escalation Call centre: semi-structured data Text mining components Data mining components Custom source Merges Standard sources Data cleansing components Mobile 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. SSIS - Architecture Visual Studio/ SQL SQL BI Studio Management Studio Package IS DTUtil Object Model IS Service Package Store msdb Cfg SSIS - Packages Control Flow Package (XML) Variables Container Event Handlers Task Connections Precedence Log Providers Task Configurations Control Flow DataFlow Task Path Source Transform Dest SSIS - Architecture DTExec SQL Management Studio Package List, Monitor IS Service Package Store msdb Cfg Package The SSIS Platform SSIS provides a platform to component developers Extensibility points Control Flow Tasks, Loop enumerators, Event handlers, Log Providers Data Flow Sources, Destinations, Transformations, Connection Managers SSIS – Custom Components Custom Tasks Derive from Task Implement InitializeTask, Validate, Execute, IDtsTaskUI Apply DtsTask attribute Custom DataFlow Components Derive from PipelineComponent Apply DtsPipelineComponent attribute Override AcquireConnections, ReleaseConnections, Validate, ProcessInput … Demo SQL Server Integration Services SQL Server 2005 Resources Web Sites MSDN Centre http://msdn.microsoft.com/sql Product site http://www.microsoft.com/sql/2005/default.asp SQL Team http://www.sqlteam.com SQL Junkies http://www.sqljunkies.com SQL Server 2005 Express Edition http://lab.msdn.microsoft.com/express/sql/default.aspx Webcasts http://msdn.microsoft.com/sql/2005webcasts Books http://www.microsoft.com/sql/2005/samplechapters.asp User Groups http://msdn.microsoft.com/usergroups/find.asp Newsgroups Microsoft.public.sqlserver.* © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.