Connecting to Oracle 9i Server using ODBC from an SSIS package

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

I use the ODBC Connection in my jobs connecting to Oracle without a problem. Generally this message occurs because the Oracle listener is down. You don't have to specify the port in the SSIS connection manager.|||

I have the DSN created using Microsoft ODBC Driver for Oracle and from the same box I can connect the Oracle server from a SqlPlus session, but when I drag an execute sql task in a SSIS package and create a ODBC connection using system datasource name. I specify the username and password. When I hit the test connection button I get the error that I mentioned earlier.

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

|||Hi,

Im runnign into the same problem. MY DSN works fine when i test in the Data Access tool while creating it. When i use it with in my activex script task it throws an error when i execute a SQL the same error. Did you find a solution for this. Please suggest. Im trying to connect from my DTS Package.

thank you,
Raja