17

I have a requirement to get the data from Oracle database to Sql Database using the SSIS.

I am using sql Server 2012 Standard Edition 64 bit and oracle is 11g.

I tried downloading the oracle drivers(64-bit Oracle Data Access Components (ODAC)) to install in Sql server with will allow me to connect to Oracle db but not able to achieve the same.

Please help me to resolve the issue.

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
user2315732
  • 365
  • 1
  • 4
  • 9

2 Answers2

19

I have had to do this on many occasions, unfortunately the Oracle website is not particularly helpful when trying to determine what you need.

You need to download a copy of "ODTwithODAC1120320_32bit.exe" which can be found at:

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

The 64-bit version is of no use, the 32 bit drivers will work just fine. Install this on your development machine and on the SQL Server box if you want to run the packages from the agent.

You can then choose the provider ".Net Providers\OracleCLient Data Provider".
The connection string will look some like this (I think 1521 is the default Oracle port):

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxxx)))

twoleggedhorse
  • 4,938
  • 4
  • 23
  • 38
  • Thanks for the response after installing the drivers do we need to do any thing on configuration? – user2315732 Aug 07 '13 at 09:10
  • ".Net Providers\OracleCLient Data Provider". am not clear on these,if am not wrong these we have to use in creating the package connection right ? – user2315732 Aug 07 '13 at 09:12
  • No configuration of the drivers is necessary. All you do is setup your connection when you are inside your package. You can add connections in your connection manager, right-click and choose "new ado.net connection" then you click "new.." where you can setup your new connection to Oracle – twoleggedhorse Aug 07 '13 at 09:22
  • can you plesae tell me which product need to install either Oracle Data access componets for oracle client or server?? – user2315732 Aug 07 '13 at 09:29
  • Just click on the link above and download the first item, ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20), this is all you need to install. Install this on your machine and on the SQL server box. It will install all the necessary components for connecting to oracle via SSIS. Once it is done you can go into sql server data tools (or BIDS if using 2008) and the provider will be there. – twoleggedhorse Aug 07 '13 at 09:37
  • yeah i have downloaded the setup from the link given by you but when i click on the setup it is asking for me to select the product like oracle Data Access componetns for Oracle Client 11.2.0.3.20 r Oracle server 11.2.0.3.20 – user2315732 Aug 07 '13 at 09:40
  • Oh, I see. Oracle Client. – twoleggedhorse Aug 07 '13 at 09:42
  • Thanks,so after installing these driver directly i can go to BIDS to create a package to connect to oracle database correct? – user2315732 Aug 07 '13 at 09:45
  • After selecting Ado.Net as a connection manager it is asking for me the provider,which provider i have to use" – user2315732 Aug 07 '13 at 10:08
  • drop down is giving me .Net provider and .Net providers for Oledb?? – user2315732 Aug 07 '13 at 10:09
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/34981/discussion-between-twoleggedhorse-and-user2315732) – twoleggedhorse Aug 07 '13 at 10:10
  • Sorry chat is not allowed to me. – user2315732 Aug 07 '13 at 10:11
  • 2
    The provider you want is ".Net Providers\OracleClient Data Provider". – twoleggedhorse Aug 07 '13 at 10:12
  • yeah i selected the same but it states am Error Message saying that "Test Connection failed because of an Error Initializing provider.ORA-12504:TNS:listener was not given the SERVICE_NAME in CONNECT_Da – user2315732 Aug 07 '13 at 10:14
  • Make sure the connection string has service name: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxxx))) obviously change the xxxx for the actual service name, or you can use SID instead. And change the IP address to be that of the oracle server. – twoleggedhorse Aug 07 '13 at 10:21
  • where i will get these connection string ?? – user2315732 Aug 07 '13 at 10:23
  • Click on the All tab and it goes in the data source box at the bottom – twoleggedhorse Aug 07 '13 at 10:24
  • Yes in Data Source i have given the server Name – user2315732 Aug 07 '13 at 10:26
  • In DataSource i have given as XXX(Server Name) do i need to give as mentioned by you?? – user2315732 Aug 07 '13 at 10:28
  • I am not finding where to mention the IP address aswell – user2315732 Aug 07 '13 at 10:34
  • You need to put the following in data source, change OracleIPAddress to the actual IP and OracleSID to the SID: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OracleIPAddress)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=OracleSID))) – twoleggedhorse Aug 07 '13 at 10:39
  • i have given the OracleIDAdress as XXX and Service_Name as ServerNameofOracle still the same error – user2315732 Aug 07 '13 at 10:45
  • For SID see http://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora – twoleggedhorse Aug 07 '13 at 10:48
  • For IP see http://www.oracle-base.com/articles/misc/identifying-host-names-and-addresses.php – twoleggedhorse Aug 07 '13 at 10:48
  • i have provided the details same like the links still the same error – user2315732 Aug 07 '13 at 10:53
  • You will have to ask your oracle database admin for the connection string, you will also have to verify that you can connect to the oracle box remotely and that the ports are correct. You have all the correct tools installed but I can't get you the connection string as I don't have access to your systems. – twoleggedhorse Aug 07 '13 at 10:56
  • Thanks buddy now i resolved the issue after changing the DataSource but is there a way to connect to oracle using the ODBC source. – user2315732 Aug 07 '13 at 11:50
  • Ask another question as this is becoming a really long comments section. – twoleggedhorse Aug 07 '13 at 12:29
  • 1
    When the connection details exceed 128 characters, the following cannot be used (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OracleIPAddress)(PORT=1521))(CONNECT_D‌​ATA=(SERVICE_NAME=OracleSID))) , Instead when you put ServeriP/DBName, it works fine. Found at http://stackoverflow.com/questions/4825552/ora-12504-tnslistener-was-not-given-the-service-name-in-connect-data – mhn Jul 07 '14 at 15:05
5

You should try and use the SSIS Connectors for Oracle (produced by Attunity). They are freely downloadable from Microsoft here. The SSIS Connectors provide optimal performance when selecting data from an Oracle db. In addition you need to setup the tnsnames.ora file with the correct connection configuration.

Stagg
  • 2,660
  • 5
  • 34
  • 32
  • If you are using SSIS 2012 and SQL Server 2012 use this link to download the SSIS connectors: http://www.microsoft.com/en-us/download/details.aspx?id=29283 – qxotk Jan 09 '14 at 21:07
  • 6
    SSIS Connectors for Oracle (produced by Attunity) only work for **Enterprise or Developer** Editions of SQL Server *not* Standard. See [MS System Requirements](http://www.microsoft.com/en-us/download/details.aspx?id=44582) – Fordy Feb 20 '15 at 11:36
  • Why use the Connectors over the Oracle tools or drivers? – Paweł Czopowik Jun 09 '17 at 17:43