July 14, 2011 at 3:36 pm
Hello everyone,
I created an SSIS package with an OLEDB source connected to Oracle 10g using the "Oracle Provider for OLEDB" provider.
We have both Oracle32 and Oracle 64 bit clients(version 11.2.0) intsalled on the server. I tested the connection and previewd data sucessfully.
But when I run the package, I get the error below. Could you please suggest. There are several different articles and I tried duplicating the C:/Program Files(x86) to C:/ProgramFilesx86 as suggested in one of the articles (http://blogs.msdn.com/b/jorgepc/archive/2008/02/12/ssis-error-dts-e-cannotacquireconnectionfromconnectionmanager-when-connecting-to-oracle-data-source.aspx), but it did not work in my case. Please suggest if there is anything else I can do.
SSIS package "UpdateWithLatest.dtsx" starting.
Information: 0x4004300A at stImport, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at UpdateWithLatest, Connection manager "aaaaa": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12557: TNS:protocol adapter not loadable".
Error: 0xC020801C at stImport, BBOM [280]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "aaaaa" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at stImport, SSIS.Pipeline: component "BBOM" (280) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at stImport, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at stImport: There were errors during task validation.
SSIS package "UpdateWithLatest.dtsx" finished: Failure.
Thanks and Regards,
Praveena
July 15, 2011 at 7:24 am
venus.pvr (7/14/2011)
We have both Oracle32 and Oracle 64 bit clients(version 11.2.0) intsalled on the server. I tested the connection and previewd data sucessfully.
You need to have the correct version on the clients, either 32 bit or 64 bit.
Where did you test from, the client or the server.
What version are you using in your connection string?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 8:17 am
Welsh Corgi ,
I am testing it from the server.
My connection string:
Provider=OraOLEDB.Oracle.1;Data Source=aaaaa;Persist Security Info=True;Password=***;User ID=uid1
Thanks and Regards,
Praveena.
August 11, 2011 at 4:15 am
Please check the connection strings for connecting to the oracle database.
datasource should be like SERVERNAME/DATABASENAME in oracle and your schema should have access to that database.
August 11, 2011 at 4:41 am
Can you create and test an ODBC Connection?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 11, 2011 at 6:52 am
@welsh,
I can test the ODBC connection. Actually now, I scheduled the package as a SQL Agent job to run as 32 bit, and it is working fine. I can run the package with dtexec utility too. The only problem is with running from BIDS. When I run from BIDS I get the same error.
Thanks and Regards,
Praveena
August 11, 2011 at 9:01 am
If you get the error from BIDS then you have a permissions issue with the Account that you are using to logon.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 11, 2011 at 9:18 am
@welsh,
I am actually using the same credentials both in the SQL Server Agent job and BIDS. What kind of added permissions will be needed to run from BIDS. Please advise.
Thank you very much for your responses.
Praveena
August 11, 2011 at 9:26 am
Usually you do not logon using the SQL Server Agent Account.
Are you using a service account for the SQL Server Agent?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 11, 2011 at 10:14 am
@welsh
Sorry for the confusion I created. I meant I used the same credentials to connect to oracle server in the datasource.
When running as a job, I actually encrypted the packages and specified the password information in the step that runs the package.
When running from BIDS as I was developing, I used EncryptSensitiveWithUserKey as my package protection level, but it did not work. Later however when I ran the package with the same protection level using the dtexec wizard by double clicking the package, it worked fine. So as I was developing I had to run the package using the wizard and then look for errors rather than using BIDS to debug.
I have the job done already. But I was curious why that happened.
Thanks and Regards,
Praveena.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply