SQL Server Integration Services with Oracle Database 10g.docx

1、SQL Server Integration Services with Oracle Database 10gSQL Server Integration Services with OracleDatabase 10gSQL Server Technical ArticlePublished: May2008Applies To: SQL ServerSummary: Microsoft SQL Server (both 32-bit and 64-bit) offers best-of breed data integration facilities in the form of SQ

2、LServer Integration Services (SSIS). This paper describes how you can use SSIS to easily interface with other data sources running on the Oracle Database10g database platform.CopyrightThe information contained in this document represents the current view of Microsoft Corporation on the issues discus

3、sed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.This White Paper is for in

4、formational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, sto

5、red in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks,

6、copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual prop

7、erty. 2008 Microsoft Corporation. All rights reserved.Microsoft and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.The names of actual companies and products mentioned herein may be the trademarks of their respective own

8、ers.ContentsIntroduction 1About SSIS 1Getting Started 2Installing the Oracle Database 10g Client Software 2Testing the Oracle Database 10g Client Installation 4Building an ETL Solution in SSIS 7Planning the Solution 7Creating the Solution 7Enhancing the Solution 13Data Conversion 13Derived Columns 1

9、6Conclusion 19IntroductionThis paper focuses on the advantages of using SQLServer Integration Services to extract data from heterogeneous sources and import data into Microsoft SQLServer for Business Intelligence (BI) analysis and reporting. Oracle Database10g data is used as the primary data source

10、. The audience for this paper includes IT professionals, database administrators, and system architects. The reader should have a general understanding of databases and Microsoft SQLServer and Oracle Database10g. Readers should use the referenced databases on their preferred hardware platform. SQL S

11、erver is part of the Microsoft integrated BI platform, and covers data warehousing, analytics and reporting, score carding, planning, and budgeting. SQLServer is in the Leaders Quadrant in both Gartners Magic Quadrant for BI Platforms and Magic Quadrant for Data Warehousing. Microsoft includes excel

12、lent BI products in both SQL Server Standard Edition and Enterprise Edition. These include SQLServer Integration Services (SSIS), SQLServer Reporting Services (SSRS), and SQLServer Analysis Services (SSAS). In contrast, Oracle offers similar functionality as an option at an additional cost with Orac

13、le Enterprise Edition. For a detailed comparison of the cost of SQLServer BI tools compared with similar Oracle products, see the Understanding Database Pricing white paper.The primary focus of this paper is SQLServer Integration Services. SSIS provides support for both heterogeneous and homogeneous

14、 environments and serves as an integration tool for customers who use multiple data sources and platforms running in Microsoft and non-Microsoft software environments. We demonstrate how easy it is to set up SSIS with a heterogeneous data source and import data into SQLServer. We also document the s

15、teps to import data from an Oracle Database10g data source into SQLServer.Many IT managers strive to adopt practical, price-efficient solutions to support their business processes. We re-emphasize the value that SQLServer offers for BI solutions. SQLServer includes excellent BI tools at no additiona

16、l charge, a significant value that IT managers cannot ignore.About SSISSQL Server Integration Services (SSIS) is a premier data transformation framework built on top of Microsoft SQLServer. It performs a wide variety of tasks from simple import/export operations to complex high-performance extract,

17、transform, load (ETL) tasks between heterogeneous data sources. This powerful functionality comes from a tightly knit suite of tools made up of control-flow and data-flow logic designers, utilities to build and execute self-contained packages, and the services necessary to support the execution and

18、automation of high-performance data transformation.SSIS originated in previous versions of SQLServer as Data Transformation Services (DTS)a simple data transformation framework built within SQLServer. What DTS lacked in broad functionality, it made up for with a simple “get-it-done” mentality. This

19、enabled developers and database administrators to extract, transfer, and load data between Microsoft SQLServer and other database platforms or data sources in a straightforward manner. However, as the needs of the database community changed, so did the need for a more complete ETL solution within SQ

20、LServer.SSIS is the end result of years of customer feedback and refinement by Microsoft. The basic premise of data transformation within SQLServer has not changed, but the tools and processes to complete ETL tasks have changed radically. SSIS contains vastly more functionality than its predecessor.

21、 This document provides a practical example of extracting and transforming data from an external data source to a SQLServer database by using SSIS. The source of the data in our example originates from an Oracle Database10g database. This particular example may become more commonplace as the total c

22、ost of ownership between Oracle and SQLServer increases. IT decision-makers recognize cost as an important purchase criterion when deciding which database platforms to run their business and SQLServer comes with the programs necessary for building BI solutions. Getting StartedBefore we start our exa

23、mple ETL process, we must first define the communication path between the source Oracle database and the destination SQLServer database. This requires installing the necessary Oracle support software. Oracle requires network transport facilities known as Oracle Net to communicate with database servi

24、ces. Oracle Net is analogous to the SQLServer Tabular Data Stream (TDS) transport facility. The most recent 32-bit and 64-bit versions of the Oracle Database10g client software are available for download at the following locations:Attention should be paid to the particular version of the client soft

25、ware being installed32-bit or 64-bit. Install the correct version for your operating system (32-bit or 64-bit). Installing the Oracle Database 10g Client SoftwareAfter you download and extract the client software archive file, navigate to the main directory where the installation files are stored, a

26、nd then use the following procedure.To install the Oracle Database 10g client software1. Double-click the file setup.exe. This launches the Oracle Universal Installer. A welcome screen similar to the following figure appears. Select Custom, and then click Next.Figure12. The next screen prompts for a

27、 home name and directory path for the installation. If necessary, edit the information. Click Next to continue.Figure23. Select the components to install. In addition to the default selections, make sure to also select Oracle Windows Interfaces and Oracle Net. Click Next.Figure34. The next screen sh

28、ows the status of product-specific prerequisite checks as they are performed. Make sure that all checks succeed and correct any issues if necessary. Click Next.Figure45. At the prompt for the port number of the Oracle Services for Microsoft Transaction Server (Figure5), click Next to accept the defa

29、ult port.Figure56. A summary screen shows all tasks the Oracle Universal Installer will perform. Verify that the information is correct. Click Install to begin the software deployment.7. After the software deployment is complete, the Oracle Net Configuration Assistant starts and guides you through t

30、he process of configuring the Oracle Net software. Configure the settings for your environment. The following options were used for this example:OptionValuePerform Typical Configuration No (unselected)Selected Naming MethodLocal NamingService NameORCLNetwork ProtocolTCPHost NameADAMSPort Number1521P

31、erform TestYesNet Service NameORCL8. When the Oracle Net Configuration Assistant completes, the Universal Installer indicates that the installation is complete. Click Exit to close the installer.Testing the Oracle Database 10g Client InstallationAfter the client software is installed, it is wise to

32、test and verify the installation by executing a basic data import from Oracle into SQLServer.To test the Oracle Database 10g client installation9. From SQL Server Management Studio, right-click a database that you can use to perform a test import, select Tasks, and then select Import Data.10. At the prompt to specify a dat