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.

Improve this question

2 Answers 2

Reset to default
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)))

Improve this answer
Sign up to request clarification or add additional context in comments.

29 Comments

Thanks for the response after installing the drivers do we need to do any thing on configuration?
".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 ?
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
The provider you want is ".Net Providers\OracleClient Data Provider".
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 stackoverflow.com/questions/4825552/…
|
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.

Improve this answer

3 Comments

If you are using SSIS 2012 and SQL Server 2012 use this link to download the SSIS connectors: microsoft.com/en-us/download/details.aspx?id=29283
SSIS Connectors for Oracle (produced by Attunity) only work for Enterprise or Developer Editions of SQL Server not Standard. See MS System Requirements
Why use the Connectors over the Oracle tools or drivers?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.