[Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

oracle ssis odbc ora-12170
Coco picture Coco · Apr 22, 2014 · Viewed 23.6k times · Source

I created a SSIS package to pull data from Oracle database to SQL server database. I have set up ODBC connection successfully on my local machine and on the server, I can connect to Oracle database using sqlplus on both environments. And my package run fine on my local machine. it also executed successfully when I run it from Package store on the server but the problem is when I run the package as sql job I got this error:

Description:

System.Data.Odbc.OdbcException: ERROR [HY000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection OdbcConnectionString constr OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options Object poolGroupProviderInfo DbConnectionPool pool DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open()

Does it make any senses?

Answer

Cameron Kerr picture Cameron Kerr · Apr 26, 2014

ORA-12170 indicates that the TCP connection to the database listener failed.

If this is an error that happens all the time, then check your connection strings (particularly port numbers), firewalls and network ACLs.

If this is a transient error that doesn't always occur, or it the firewalls and network ACLs seen okay, then check for correct ARP behaviour. I've recently had this problem myself, and have blogged about it at http://distracted-it.blogspot.co.nz/2014/04/ora-12170-tnsconnect-timeout-resolved.html

Hope it helps.

Related questions

How to connect to Oracle db from SSIS/SQL Server Data Tools?

I have Oracle 11g r2 client installed on my 64 bit machine running Windows Server 2008 r2. Have the following installed: SQL Server 64 bit MS Office 64 bit I tried connecting from excel/powerpivot to the oracle db, it works perfectly fine. However, …

Read More
SSIS - the value cannot be converted because of a potential loss of data

I am relatively new to SSIS. I am trying to extract information from an Oracle database using Microsoft OLEDB for Oracle and I am using this query: SELECT ID FROM Test I get an error message saying: the value cannot …

Read More
Oracle OLE DB Provider not Listed in SSIS

I'm having an issue with SSIS and VS2015 CM. I have a package that needs to connect with Oracle to get some data. I installed the ODAC for Win64 and the Oracle client but I can't see the Oracle provider …

Read More