Getting data from Oracle database into SQL Server database tables

SQL Server's SSIS can move data from Oracle to SS also. Bigger companies often have security guidelines that make linked servers a hassle and so SSIS is an easy way around that kind of thing.

David Todd, any tips on how to create linked server between SQL Server database and Oracle db on UNIX?

David, in the line where you are creating linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'EXSTOCK', @srvproduct=N'OraOLEDB', @provider=N'OraOLEDB.Oracle', @datasrc=N'PROD'


@datasrc parameter is the name of the database or is it the tns entry name from tnsnames.ora file?

and also is @srvproduct value OraOLEDB an actual value or a sample value?

David, if I decide to create an SSIS package to transfer the data on regular basis, do I still need to create a linked server separately, or will it be created in the process of SSIS creation?

I also noticed that OraOLEDB.Oracle provider is not installed on the SQL Sever. What do I need to install there in order to have that provider? Oracle client 64-bit is already installed

This is an oracle drivers it can be downloaded from oracle.

http://www.oracle.com/technetwork/database/windows/utilsoft-088126.html

Thanks David.

Would you be so kind to also help me on this question?

https://www.experts-exchange.com/questions/28355135/Connecting-to-oracle-from-SSIS.html

There I am trying to figure out how to go about conecting to Oracle db on AIX operating system.

Praveen, the link you posted seems to only contain older Oracle versions, up to 10g, and I am dealing with 11g.

Thank you!