September 1, 2008 at 7:06 am
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
September 1, 2008 at 7:20 am
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.
September 1, 2008 at 5:11 pm
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]
September 2, 2008 at 7:40 am
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
September 2, 2008 at 7:44 am
Many thanks.
Now all I need to do is get my linked server working!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy