June 26, 2009 at 2:42 pm
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.
May 18, 2012 at 1:41 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply