Best way to retrieve data from Oracle Database?

[Swinx]

Hi Guys

I am having a bit of an issue. I need to find the best way to get data out of a Oracle 9 database.

I am working on a project where we need to build Scorecards for a corporate client. The data that we need resides on an Oracle database. I need to pull the data into SSIS and then manipulate and write it to our data warehouse.

The client's Oracle DBA has provided us with a stored proc that returns the data that we need for our first iteration of the project. The only problem is that the proc returns the results in a REF CURSOR.

I am a MS SQL programmer and have no Oracle experience (and very limited .NET experience). I have been informed that you cannot pass the results with a simple select as in MS SQL, it has to be passed in a REF CURSOR.

I have been searching for a way to use this proc in SSIS but there seem to be no way for me to use it.

I am currently considering two possibilities:
1. Use a Script Component as a data source in SSIS
2. Have the proc changed to populate a table instead of returning the REF CURSOR

What would be the best way to get the data from the Oracle Database? Are there any other ways that I could use?

 

[SQLBI]

Hi,

Personnally i'd dump the result of the Oracle statement to a flat file or a staging table in my SQL DB and then use SSIS to do the rest of your ETL process.

Been having to query an Oracle DB myself for reporting recently and oh how i wish it was SQL Server !

Cheers,
Leigh

The problem with common sense is that it isn't that common!