SQL Server Integration Services for Oracle 10g

1. Introduction

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

Readers of this article include IT professionals, database administrators, and system architects. Readers should have a general understanding of databases and Microsoft SQL Server and Oracle database 10g. Readers should use a reference database on their preferred hardware platform.

SQL Server is part of Microsoft's integrated business intelligence platform and covers data warehousing, analysis and reporting, scorecards, plans, and budgets. SQL Server is in the leader quadrant in the Magic quadrant of the Gantt Business intelligence platform and in the Magic quadrant of the Data Warehouse. Microsoft has excellent business intelligence 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). Oracle, for its part, provides similar functionality as an option for Oracle Enterprise Edition, but requires an extra charge.

The primary focus of this article is SQL Server Integration Services. SSIS provides support for heterogeneous and homogeneous environments and serves as an integration tool for customers working with multiple data sources and platforms running in Microsoft and non-Microsoft software environments. We described how easy it is to build SSIS and import data to SQL Server with a heterogeneous data source. We also documented the steps for importing data from an Oracle database 10g data source to SQL Server.

Many IT managers are working to support their business processes with practical, cost-effective solutions. Again, we emphasize the value of SQL Server for Business intelligence solutions. SQL Server includes excellent business intelligence tools without additional charges that IT managers cannot overlook.

2. About SSIS

SQL Server Integration Service (SSIS) is the primary Data transformation framework built on top of Microsoft SQL server. It performs many tasks, from simple import/export operations to complex high performance extraction, transformation, loading (ETL) tasks between heterogeneous data sources. This powerful feature comes from a tightly knit suite of tools that includes control flow and data flow logic designers, tools for building and executing stand-alone packages, and services necessary to support this execution and automation of high-performance data transformations.

SSIS originates from the previous version of SQL Server as Data Transformation Services (DTS), a simple Data transformation framework built into SQL Server.

SSIS as Data Transformation Services (DTS), a simple transformation framework built into SQL Server, appears in a previous version of SQL Server. What DTS lacks is a wide range of features, and it is only released with a simple idea of "want to do it". This allows developers and database administrators to extract, transform, and load data directly between Microsoft SQL Server and other database platforms or data sources. However, as the requirements for database operations change, the need for a more complex ETL solution in SQL Server has changed.

SSIS is the end result of many years of customer feedback and Microsoft modifications. The basic premise of Data transformation in SQL Server has not changed, but the tools and processes to complete the ETL task have fundamentally changed. SSIS has more extensive functionality than it did before. This document provides a practical example of using SSIS to extract and transform data from an external data source to a SQL Server database. The data source in this example is from an Oracle 10g database. This example may become more generic as the total cost of ownership increases between Oracle and SQL Server. It decision makers view costs as an important purchase basis when deciding which database platform to use to run their business, and SQL Server has the necessary procedures to build a business intelligence solution.

Begin

Before we begin our ETL process sample, we must first define the contact path between the source Oracle database and the destination SQL Server database. This requires Oracle support software that is required for installation. Oracle requires network transport tools such as Oracle NET to communicate with other database services. Oracle NET is similar to the SQL Server Tabular Data Stream (TDS). The latest 32-bit and 64-bit versions of Oracle Database 10g client software can be downloaded from here:

Http://download.oracle.com/otn/nt/oracle10g/10201/10201_client_win32.zip

Http://download.oracle.com/otn/nt/oracle10g/10201/102010_win64_x64_client.zip

Be careful to install a specific version of the client software-32-bit or 64-bit. Install the correct version for your operating system (32-bit or 64-bit).