Como Hacer Conexion Oracle Con Sql

SQL Server Integration Services with Oracle Database 10g

SQL Server Technical Article

Published: May 2008

Applies To: SQL Server

Summary: Microsoft SQL Server (both 32-bit and 64-bit) offers best-of breed data integration facilities in the form of SQL Server Integration Services (SSIS). This paper describes how you can use SSIS to easily interface with other data sources running on the Oracle Database 10g database platform.

Copyright

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed 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 informational 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, stored 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, 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 property.

 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 owners.

Contents

Introduction 1

About SSIS 1

Getting Started 2

Installing the Oracle Database 10g Client Software 2

Testing the Oracle Database 10g Client Installation 4

Building an ETL Solution in SSIS 7

Planning the Solution 7

Creating the Solution 7

Enhancing the Solution 13

Data Conversion 13

Derived Columns 16

Conclusion 19

Introduction

This paper focuses on the advantages of using SQL Server Integration Services to extract data from heterogeneous sources and import data into Microsoft® SQL Server™ for Business Intelligence (BI) analysis and reporting. Oracle Database 10g data is used as the primary data source.

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

SQL Server is part of the Microsoft integrated BI platform, and covers data warehousing, analytics and reporting, score carding, planning, and budgeting. SQL Server is in the Leader’s Quadrant in both Gartner’s Magic Quadrant for BI Platforms and Magic Quadrant for Data Warehousing. Microsoft includes excellent BI products in both SQL Server Standard Edition and Enterprise Edition. These include SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and SQL Server Analysis Services (SSAS). In contrast, Oracle offers similar functionality as an option at an additional cost with Oracle Enterprise Edition. For a detailed comparison of the cost of SQL Server BI tools compared with similar Oracle products, see the Understanding Database Pricing white paper.

The primary focus of this paper is SQL Server Integration Services. SSIS provides support for both heterogeneous and homogeneous 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 SQL Server. We also document the steps to import data from an Oracle Database 10g data source into SQL Server.

Many IT managers strive to adopt practical, price-efficient solutions to support their business processes.

...