OracleClient requires Oracle client software version 8.1.7 or greater

I developed a loading script in 32 bit MS visual studio and the script running fine in Visual Studio. The script is about loading oracle data into MS SQL database.

I have 64 bit Windows 2008 R2 server with 64 bit MSSQL 2008 server

I created a MS SQL job to attach the loading script but the script is failed to run with the following error in job history

I don't understand why the script running fine in Visual Studio but when it attached to the MSSQL job and it ran failed.


Message
Executed as user: domain\login name. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.4000.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:01:00 AM  Error: 2011-10-17 00:01:09.99     Code: 0xC0047062     Source: Load UnsubscribedEmails ADO NET Source [1]     Description: System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.     at System.Data.OracleClient.OCI.DetermineClientVersion()     at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)     at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)     at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     at System.Data.OracleClient.OracleConnection.Open()     at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)     at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)  End Error  Error: 2011-10-17 00:01:09.99     Code: 0xC0047017     Source: Load UnsubscribedEmails SSIS.Pipeline     Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131500.  End Error  Error: 2011-10-17 00:01:09.99     Code: 0xC004700C     Source: Load UnsubscribedEmails SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-10-17 00:01:09.99     Code: 0xC0024107     Source: Load UnsubscribedEmails      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:01:00 AM  Finished: 12:01:10 AM  Elapsed:  9.39 seconds.  The package execution failed.  The step failed.


I spent a lot of time trying to fix this issue. But all failed. Please help. it's kind of urgent
wasabi3689Asked:
Who is Participating?
 
KlausBarthelsConnect With a Mentor Commented:
Do you use a ODBC-Connection to the ORA-DB?
If you do so, then you need also the ORA-Client.
Without ORA-Client you have no information about the connection (TNSNAMES.ORA and so on) and there are no driver to connect to database.

Regards

Klaus
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
If you use ODBC to connect to an Oracle database, you need either the Microsoft ODBC driver for Oracle, or an Oracle client that includes an ODBC driver.

Please tell us how you connect to Oracle.  Also, do you know which Oracle database version you are trying to connect to?

(I may be an Oracle expert, but I'm not an expert on Visual Studio, or SQL Server, so I can't explain why a connection to Oracle worked from one of those but not from the other.)
0
 
wasabi3689Author Commented:
I use oracle client to connect to oracle database. oracle database is 8i. Microsoft ODBC driver for Oracle in server is not support from Microsoft, and cannot do this approach.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
OK, that should work. First, you need to configure the Oracle client to connect to the Oracle database.  Did you do that?  Then, do you have an ODBC data source set up in your WIndows Control Panel that uses the Oracle ODBC driver and the database connection you set up in the Oracle client?  Did you test that in Control Panel and does that ODBC data source work there?
0
 
lcohanConnect With a Mentor Database AnalystCommented:
I know is a different direction and you worked hard already but its much easier (and faster with better control) in my opinion to use OLEDB driver and a ORACLE linked server in SQL then just use full name qualifiers like:
ORA_Server_name.ORA_db_name.ORA_schema_name.ORA_table_name to do your transfers in a plain T-SQL job step instead of VS code...

http://support.microsoft.com/kb/280106
0
 
wasabi3689Author Commented:
I ever use OLEDB connection, but it doesn't work with the same error message. There is no microsoft ODBC for oracle in 64 bit Windows 2008 server.
0
 
lcohanConnect With a Mentor Database AnalystCommented:
I know as a fact that on Windows 64bit Windows you can use 32 bit ORACLE driver to connect to ORACLE databases but not viceversa.

Please see solution at link below and hope it will help you as well.
http://social.technet.microsoft.com/Forums/en-US/windowsserver2008r2general/thread/dae6f490-30c8-4799-a048-37f103f0088d/

0
 
wasabi3689Author Commented:
have anyone tried this approach?

http://www.devart.com/dotconnect/oracle/download.html

what is your feedback?
0
 
wasabi3689Author Commented:
Answers provided useful info for resolving the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.