64-bit And Linked Server To Oracle

We're having some trouble connecting SQL Srvr2005 to Oracle (10g) in our 64-bit environment. We have Oracle's 32-bit Ole Db provider installed and have been able to use that in SSIS to connect and return some data running in 32-bit debug mode (as long as the query isn't long, complicated or using parameters =)

However, we have some hefty queries that we need to gather some data and want to put them in a stored proc on Oracle and call it through SSIS. We tried adding our Oracle DB as a linked server and are getting some errors. I've tried the following code replacing the provider with 'OraOLEDB.Oracle.1', 'OraOLEDB.Oracle' as well as 'MSDAORA':


EXEC sp_addlinkedserver

'ORA_TSTW', 'Oracle',

'OraOLEDB.Oracle.1', 'TSTW'



EXEC sp_addlinkedsrvlogin 'ORA_TSTW', false,

'user', 'lituser',

'pw'



select * from openquery(ORA_TSTW, 'select * from lituser.customer')


We keep getting the error for each of the providers we try. I've tried it in Management Studio as well as in an Exec SQL task (with debug set to 32-bit) to no avail.


Cannot create an instance of OLE DB provider "OraOLEDB.Oracle.1" for linked server "ORA_TSTW".


I've checked and the MSDAORA.dll is registered in both C:Program Files (x86)Common FilesSystemOle DB as well as C:Program FilesCommon FilesSystemOle DB.

If we can't where we can call an Oracle stored proc, we were thinking of just using a script task to connect to Oracle and retrieve the data we need, but I have a feeling performance this way would be degraded.

Anyone have thoughts/suggestions? TIA!

Sabrina