Best way to extract Oracle 8i data into a SQL Server2005 database?

Question

Best way to extract Oracle 8i data into a SQL Server2005 database?

Paula-196779

SSCrazy

Points: 2575
More actions
September 1, 2008 at 7:06 am

#197103

Hello all,
I wondered if I could get some advice from people. I need to extract data from an Oracle 8i database which is sat on a Unix server, and load it onto a new database on SQL Server 2005. I am thinking my best option is to create a linked server, then create a SSIS package to import data into the new database. Problem is I have no idea how to create a linked server using a Unix platform. I have linked servers on SQL 2000 that use databases on Windows platforms but none on SQL 2005 using Unix. Does anyone know how I go about this?
Secondly, if anyone can tell me a different and better way of achieving my goal, I'd like to hear about that too. The only other option I am aware of is to go to the Oracle side and extract the data using sqlplus into flat files. I think that will take a long time to format etc.
So any help will be much appreciated!
Many thanks,
Paula

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paula-196779 SSCrazy Points: 2575 More actions · Answer 1

Forgot to say, I know there are a lot of posts around this topic in this forum, and I don't wish for people to repeat everything, but I couldn't find anything specific to Unix, so felt the need to start a new post. Hope no-one minds!

Thanks.

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 2

Same topic is discussed here

http://www.sqlservercentral.com/Forums/Topic533065-1042-1.aspx#bm533104

Regarding Unix, there is no difference in procedures whether the Oracle instance is on Unix or windows. You are dealing with the Oracle database only not the OS.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 3

Paula,

I try to avoid loading SSIS whenever it's practice. If all you want to do is extract data from one table/view into a SQL Server table then my suggestion would be to use the OpenQuery method. Example:

insert sqlTable

( col1, col2...)

from openquery(LinkedServerName, 'select col1, col2.. from oracleTableOrView')

I've found that to be the most effective route but this would also work...

insert sqlTable

( col1, col2...)

SELECT COL1, COL2...

FROM LINKEDSERVERNAME..SCHEMA.ORACLETABLEORVIEW

Please note that the syntax above is case sensitive on the Oracle side of the house.

Good Luck.

--Paul Hunter

Paula-196779 SSCrazy Points: 2575 More actions · Answer 4

Many thanks.

Now all I need to do is get my linked server working!