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?
Best Answer
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.