SQL Server 2000 DTS / 2005 IS (mskang@microsoft.com) RRE / CSS / Microsoft SQL Server & Analysis Service
genda SQL Server 2000 DTS SQL Server 2005 SSIS
(M&A) BI
SQL Server, Oracle, DB2, Sybase, Infomix, MySQL, ACCESS, FoxPro TEXT, ISAM XML, EDI, CVS, EXCEL, Web Services, COM+, EJB B2B EAI B2B EAI BPM
(2) B2B EAI BPM SQL Server 2000 DTS SQL Server 2005 SSIS SQL Oracle Replication SQL Server 2000 DTS SQL Server 2005 SSIS Bcp BULKINSERT BizTalk Server 2004 MSMQ BizTalk Server 2004 MSMQ ODBC, JDBC, OLEDB ODBC, JDBC, OLEDB Adapters Adapters
QL Server 2000 DTS Data Warehouse System Components SQL Server 2000 BI/DW SQL Server 2000 DTS
W System Components Data Sources Staging Area Data Warehouse Data Marts End User Data Access Data Input Data Access
SQL Server 2000 BI/DW Data Sources Intermediate Data Stores Warehouse Servers Business Intelligence SQL Server RDBMS SQL Server RDBMS/ OLAP Services Excel, EQ & Third Party Third Party OLEDB XML OLEDB XML OLEDB XML SQL Server RDBMS & DTS OLAP Services OLAP Services Third Party Data Mining Services XML XML XML XML Microsoft Meta Data Services
SQL Server 2000 DTS ODBC, OLEDB, DTS (RBDMS, ODBMS) TEXT, EXCEL, IMAGE FILES IN FOLDER, xbase Files Upgrade or Migration Batch Task Online Task (ex> 2 Phase Commit) Scripting ODBC Oracle Mainframe DB2, VSAM, CICS/IMS OLE DB Source Transforms OLE DB Destination DTS Data Pump In Out DB2 VSAM, OS/400
DTS DTS Package Designer Package Connections Data Packages
DTS Package Designer DTS Designer MMC menu DTS Package Designer menu DTS Designer Toolbars DTS Package Designer toolbar Task toolbar Connection toolbar Design Sheet Connections Tasks Steps
Connections (1) Understanding Connections Data Sources OLE DB ODBC Connection Categories File connections Database connections Microsoft Data Links Configuring Connections Connection (Unique Name) Data Source Connection Data Access
Connections (2) Deploying Connections Connections (parallelism). connection Connections (parallelism) connection. Maintaining Connections Connection connections user names, passwords, databases for database connections Changing file paths for file connections
Data Transform Data Task Transforms and Inserts Data Data Driven Query Task Performs Flexible Transact-SQL Operations ParallelDataPumpTask Processes Hierarchical Rowsets
Packages Packages SQL Server SQL Server Meta Data Services Structured storage file Visual Basic file Packages Executing packages interactively Reviewing execution results
SQL Server 2000 DTS DTS Package Designer Package Connections Data Packages
SQL Server 2005 Business Intelligence Integrate l l Data acquisition from source systems and integration Data transformation and synthesis Analyze l l Data enrichment, with business logic, hierarchical views Data discovery via data mining Report l l Data presentation and distribution Data access for the masses
Integration Services in the SQL Server 2005 Analysis Enterprise SQL Server Management Studio Manager Manager Reporting Services (Reporting) Analysis Services (OLAP & DM) Integration Services (ETL) SQL Server (RDBMS) BI Development DTS Big ETL Highest end scale and throughput Rich connectivity Advanced process flow Branches, loops New : Data Pipeline Streamed transformations Programmability CLR based transforms Rich debugging Major productivity & usability enhancements
BI Development Studio DW/OLAP, : Relational, OLAP, DM, DTS, Reporting, Code, Web pages : Visual Studio.NET,,
SQL Server 2005 SSIS ETL Visual Debugging Task
SQL Server 2005 SSIS Alert & escalation Call centre: semi-structured data Text mining components Data mining components Mobile data Custom source Merges Warehouse Legacy data: binary files Standard sources Data cleansing components Application database SQL Server Integration Services Reports
QL Server 2005 SSIS Architecture Standard transforms Custom transforms Data Source Adapters Data Destination Adapters Package Loops & Sequences Tasks XML Package XML Package Event Handlers Wizards DTS Designer Command Line
Life Cycle tools Design Business Intelligence Designer Migration wizard for pre SQL 2005 packages Visual Source Safe Integration Deployment Configuration Wizard: flexible package configuration Deployment Utility: Install packages SQL Agent: Schedule package execution Command Line Utility: Execute packages Supportability SSIS service to monitor running packages and stored packages Rich Logging Checkpoint - Restart ability WMI Integration
Integration Service Overview(1) Connection Manager Package object Layer between environment and outside world Allows for indirection, configuration Manages opening, closing, sharing Used for both tasks and transformations Flow Types Two different types of flow Control Flow = Runtime = Tasks Data Flow = Pipeline = Transforms Managed in Designer Used to be single view in SQL 2000 Separate views in SQL 2005
Integration Service Overview(2) Data Adapter Data Flow object Logical use of data Data Flow Source Data Flow Destination Links to Connection Manager Metadata Validation Confirm and link columns in source and destination Define source first, then flow data to destination. Destination responds. Changing the column definition invalidates the contract with the destination
How does it work? Control Flow Data Flow FTP Flat File Source Oracle ADO.NET Source Loop Send Mail Merge Execute SQL De-duplicate Data Flow SQL Server Split Flat File
xecution Command-line execution DTEXEC User Interface execution DTEXECUI Can generate command line for DTEXEC Scheduling SQL Server Agent
SIS (Performance) Operation, Data flows. (Facility) adapter transformation coding. object model scripted component. Highly productive visual environment speeds development and debugging Smarts Data cleansing features enable difficult data to be handled during loading Data mining brings intelligent handling of data for imputation of incomplete data, conditional processing of potential problems, or smart escalation of issues such as fraud detection
Integration Services
SQL Server DTS SSIS.
esources Home page www.microsoft.com/korea/sql/ www.microsoft.com/sql/ Bulk Loading http://www.microsoft.com/technet/ prodtechnol/sql/2000/maintain/ incbulkload.mspx Community www.sqljunkies.com/blogs www.sqlservercentral.com www.sqlis.com
2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.