January 7, 2010 at 3:07 am
Hello,
my requirements are to connect to oracle db and make a copy on the local sql server of 3 tables.
This operation can be done only once per day.
First, i dont know how to connect and retrieve this data from oracle inside sql server 2005.
Second, there are about 70.000 rows in one of this tables, on the other 2 there are about 3.000 rows for each tables.
Since the network that connect this 2 dbms is slow i cant just make a select * from the big table.
so basically, copy every day this 3 tables from oracle to sql server.
How can i do this?
I suppose i have to use integration services. Am i correct?
Do i need drivers to connect to oracle?if yes, Which are this drivers?
How to retrieve the big table?
thank you
January 19, 2010 at 5:08 am
Hi,
Yes you can use SSIS. I've used a Data flow Task and then an OLE DB Source to extract the data. You'd need to decide whether you
truncate and reload the data every day of append with a Slowly Changing Dimension for example.
We also have the Oracle drivers installed on our sqlserver(you can get them from you oracle installtion cd/dvd).
We then create an ODBC connection to oracle and then create a linked server. Data can then be access from management studio
via an openquery.
--Linked server creation script
/****** Object: LinkedServer [Willow] Script Date: 01/19/2010 12:06:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'Willow', @srvproduct=N'Oracle', @provider=N'MSDAORA', @datasrc=N'DEV'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Willow',@useself=N'False',@locallogin=NULL,@rmtuser=N'userid',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Willow', @optname=N'use remote collation', @optvalue=N'true'
---Example openquery
select * from openqueryWillow,'select * from table1')
I hope that helps
Paul
January 19, 2010 at 6:04 am
I have solved already installing oracle drivers and using SSIS.
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply