SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to run an SSIS package in Visual Studio 2008


Trying to run an SSIS package in Visual Studio 2008

Author
Message
asawyer 26423
asawyer 26423
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Heinrich-206378
Heinrich-206378
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 451
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

asawyer 26423
asawyer 26423
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 2
Hi,

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

Thanks
Adam
asawyer 26423
asawyer 26423
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Heinrich-206378
Heinrich-206378
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 451
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.
asawyer 26423
asawyer 26423
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search