Trying to run an SSIS package in Visual Studio 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Trying to run an SSIS package in Visual...

Trying to run an SSIS package in Visual Studio 2008

Rate Topic

Display Mode

Topic Options

Author

Message

asawyer 26423

Posted Tuesday, June 22, 2010 11:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:00 PM
Points: 4, Visits: 2

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:

SSIS package "Package.dtsx" starting.
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

Post #941496

Heinrich-206378

Posted Tuesday, June 22, 2010 11:24 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:56 AM
Points: 110, Visits: 293

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

Post #941503

asawyer 26423

Posted Tuesday, June 22, 2010 11:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:00 PM
Points: 4, Visits: 2

Hi,

Sorry I forgot to mention I have both 32bit and 64bit Oracle clients, we overcame that issue last week.

Thanks
Adam

Post #941505

asawyer 26423

Posted Tuesday, June 22, 2010 11:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:00 PM
Points: 4, Visits: 2

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

Post #941509

Heinrich-206378

Posted Wednesday, June 23, 2010 5:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:56 AM
Points: 110, Visits: 293

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.

Post #941669

asawyer 26423

Posted Wednesday, June 23, 2010 7:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:00 PM
Points: 4, Visits: 2

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

Post #942157

« Prev Topic | Next Topic »

Permissions