Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSIS connection to Oracle trouble with BIDS Expand / Collapse
Posted Friday, June 26, 2009 2:42 PM


Group: General Forum Members
Last Login: Friday, May 2, 2014 8:21 AM
Points: 168, 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.

Post #742973
Posted Friday, May 18, 2012 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 28, 2016 2:24 PM
Points: 5, Visits: 37
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.

Post #1302809
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse