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


SSIS connection to Oracle trouble with BIDS


SSIS connection to Oracle trouble with BIDS

Author
Message
Alan Spillert
Alan Spillert
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 423
I've developed an Integration Services package using the Microsoft (obsolete) OLE-DB provider to move data between Oracle 10g (on Unix) and SS05 SP3. During my initial development the Control Flow Tasks will execute successfully in BIDS. After saving the package with ProtectionLevel of "EncryptSensitiveWithUserKey" and sleeping 8 hours, when I return and open the package I can not execute a task which connects to Oracle in BIDS. The Oracle connection manager will test successfully, but running it in BIDS gives me the famous error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

I installed the Oracle 11g ODAC components including OLEDB provider, but it will not allow automatic translation of Oracle unicode to BIDS Visual Studio data format. so I've dropped it.

I have built my package (ProtectionLevel changed to "DontSaveSensitive"), deployed it with a configuration file that includes the Oracle connection information, and it works from SQL Server Agent just fine. TransactionOption doesn't seem to do much. This is not moving much data and does not need explicit transactions.

What parameter am I not setting that prevents BIDS from testing tasks that connect to Oracle?

Can anyone recommend a supported, useable OLEDB provider for Oracle that works with SSIS?

Thank you for any assistance.



MattGauch
MattGauch
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
I had this problem, too. Switching to a connection which uses the Oracle Provider for OLE DB instead of the Microsoft OLE DB Provider for Oracle did the trick.

HTH
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