Connecting to oracle and retrieving data to store on sql server

• paulm-510034

  SSC Enthusiast

  Points: 136

  More actions

  January 19, 2010 at 5:08 am

  #1105334

  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

• evald

  SSCrazy

  Points: 2165

  More actions

  January 19, 2010 at 6:04 am

  #1105368

  I have solved already installing oracle drivers and using SSIS.

  Thank you