May 31, 2012 at 10:08 am
There are several posts and blog entries about this, but none seem to fit my particular issue.
I am trying to read a view from an Oracle database and write it to a SQL table
I have installed SQL 2012 (including SSIS) on WIndows 2008 R2
I have installed the 32-bit Oracle client. I can TNSPING the Oracle database.
In Visual Studio I have created the Oracle Connection Manager and the SQL Server connection manager. Both connection managers test successfully.
I have created a data flow task
In the data flow task I have created a OLE DB Source for the Oracle Database. When I click the preview button, it returns data from the Oracle view.
I have created the OLE DB Source for the SQL database, told it to create a new table and it builds the table based on the oracle view definitions.
Everything seems perfect, then I click debug, and the Oracle connection comes back with this error:
[OLE DB Source [67]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "XXXX.XXXXXXX" failed with error code 0xC0202009. There may be error massages posted before this with more information on why the AcquireConnection method call failed.
"XXXX.XXXXXX" is the oracle user ID
All I am finding is references to this error when moving from Visual Studio to SQL Server Agent. But I am not getting that far. I don't understand how the preview button can read data, but the debug fail trying to connect to the server. I have set DelayValidation to True everywhere I can find, but that has not seemed to help.
Thank you in advance for any assistance you can provide.
June 1, 2012 at 12:58 am
What happens if you set the Run64BitRuntime property of the project to False?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 1, 2012 at 2:30 pm
Thank you very much. That resolved my issue perfectly.
Dep
June 2, 2012 at 2:39 pm
No problem, glad to help.
Remember to run the package also in 32-bit mode on the server if you also installed the 32-bit provider there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply