June 22, 2010 at 11:13 pm
Hi,
I'm trying to run a SQL Server 2008 SSIS package in Visual Studio 2008 using Attunity 64bit to talk to an Oracle 11g database (CPPROD). When I execute the package manually I get the following error:
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0x20F at Data Flow Task, Oracle Source [321]: The AcquireConnection method call to the connection
manager Oracle CPPROD Connector 1 failed with error code 0x80004005.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Oracle Source" (321)
failed validation and returned error code 0x80004005.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Success.
I am using SQL Server 2008 Enterprise Edition 64 bit, Oracle 11gR2 Client 64bit, Attunity 64 bit, Visual Studio 2008 64 bit, all on the same server. I am unsure where the problem lies - can anyone point me in the right direction?
Thanks
Adam
June 22, 2010 at 11:24 pm
Not sure if this was done on your side, but you need to install 32 bit oracle client. Check out the links below:
http://msdn.microsoft.com/en-us/library/ee470675.aspx
http://www.attunity.com/forums/microsoft-ssis-oracle-connector/error-failed-load-oci-dll-1308.html
June 22, 2010 at 11:27 pm
Hi,
Sorry I forgot to mention I have both 32bit and 64bit Oracle clients, we overcame that issue last week.
Thanks
Adam
June 22, 2010 at 11:37 pm
Some more information:
I had to put the package in the system database MSDB and then can execute the package in SQL Server Management Studio (pointing to SSIS instead of the DB) - the data is transferred from Oracle to SQL Server. It is only when I try to execute the package in VS2008 that the problem occurs.
Thanks
Adam
June 23, 2010 at 5:45 am
1. Is the machine with VS2008 the same machine where SQL Server is installed? What I am getting to is that as developers we need to have Oracle client 32/64 bit installed on our dev machines as well as on the server where SSIS is running.
2. Long shot.I checked out a link here where they speak about permission/security issues, which may effect you as well:http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/09/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx
3. We use Config files for our security , and that might cause havoc. Sometimes when one changes it through the Package Configuration Wizard, it removes the password.One then has to add it again manually.
4. I have "funnies" happening to me and when I switched the ValidateExternalMetaData to "False" it caused my Connector to run again(something stored somewhere in cache). It is also good practice once you are at the end of your development to switch it off, as it causes SSIS to validate the metadata, and if you have a couple of sources and destinations, it can take a lot of time. The same goes for BypassPrepare- I switch it to True.
5. What is your package's protectionlevel set to? We save ours as DontSaveSensitive.
Maybe you can upload some pics or more detail so we can see as it is quite difficult not being there with you.
June 23, 2010 at 7:03 pm
Hi,
Thank you so much for your advice. For the purposes of this project (just a prototype) it ended up being sufficient to run the package in the SQL Server Management Studio connected to SSIS. We just needed to create the Package in VS and then run in SQL Server MS.
Cheers
Adam
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply