Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
Are you running it on a 64 bit system? Or are you using BLOBs?
If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?
|||Hi,
I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version.
I use Oracle only to read data from using select statements, I do not use linked servers.
All other Oracle providers works on that machine but the Microsoft one.
All databases were restored from the dev backup
Before rebuilding this machine, this provider was working.
After disk format and OS install, I did rebuild the machine in this order:
1) Oracle Client
2) SQL 2005
3) VS Studio 2005
4) Restore of DB's
5) Restore of sys variables and xml configurations as well as surface config
6) Restore of logins and access settings
7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server.
Thanks,
Philippe
|||Hi,I use "Microsoft OLE DB Provider for Oracle" and it works fine.
I remember having problems with the TNS-Entry in the connection manager
that didn't match with the TNSNAMES.ORA
Did you try the "Test connection" button in the connection config?
Do you save the pwd in the connection?
What is your package protection level? When you select don't save sensitive the pwd isn't saved.
|||
Hi,
I did copy over the TNSNAME.ORA that I use in the DEV Server.
I made sure that the default domain is the same than it is in Dev in the Oracle configuration assistant and can test the connection successfully.
I do not save the sensitive in my packages, i use config files to store the connection string including the password.
This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level.
Thanks,
Philippe
|||Try changing your package to not use configuration files and instead embed the information into it.
Then try and see if it magically works.
I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail.
In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.
|||This issue is very disturbing.
Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked.
I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine).
Same error than before.
Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package...
I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:
1) Package creator Password expiration every 90 days that kills all the encrypted content
2) Difficulties to quickly deploy packages from machine to machine
I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic...
Regards,
Philippe
|||I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.|||Could this be the problem?
http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx
-Jamie
|||What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.|||hi,
This article was good.
Oracle connection is used to get Data in my case.
I get this error becasue package protection level was don't save sensitive the pwd.
So at execution time it says null password.
although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.
No comments:
Post a Comment