December 21, 2009 at 12:16 pm
Okay, I tried all the ProtectionLevel nonsense about a dozen times. Plus, we reverted all packages back to the last copies that ran. All of those settings are in the package configurations. So it's pointless to keep thinking something is configured wrong when we can't use the same package files that ran for weeks on end as they were set up when they worked.
This has to be either a global SSIS issue or an Oracle issue based on the symptoms. How else could all Oracle OLEDB access within SSIS break at once even in packages we didn't touch?
Does anyone know exactly the steps that are taken when (a) a "save password" checkbox is set in in an Oracle OLEDB connection manager editor and the editor is closed; (b) how that same connection manager attempts to get the password saved in the package back out for use when it's re-opened? If those steps all take place within SSIS, then we know it's not Oracle because I can't save a password that works. And I've tried using that "save password" setting with ALL ProtectionLevel settings. And I can see the password setting in the package XML appearing to be correct (and matching what was in there when it last worked).
December 21, 2009 at 3:51 pm
Well, a full reinstall of the Oracle client software (Oracle ODAC 10.2.2.21) fixed things. Twice before I had tried installing the 11g ODAC software and pointing to it and that changed nothing. When I did the full reinstall I had to change registry entries Oracle appeared to miss. So that may explain why switching to the 11g copy had no effect -- it may have still been referencing things in the 10g code path.
I still don't get why reinstalling Oracle would matter here though.
One other note: None of the OLEDB Oracle components show anything in the password fields. Before, I'm pretty sure I saw asterisks. Attunity shows asterisks. The flows run but each time I want to edit the connection manager or bring that up to use the "Test Connection" option, the password is gone and must be re-entered. Am I imagining that these used to show blanks and also pull the saved password back into the component editor? The flows run now regardless so that's a smaller point but a point of confusion nonetheless ... it seems wrong for different components to have different behavior here (confusing).
XML configurations also work now. That was the original path I went down that started all this.
I'm very puzzled about how trying configurations in SSIS could affect the Oracle install so that a reinstall was needed to fix things ... I'd be very interested in hearing any information on that.
December 21, 2009 at 4:03 pm
Well, a full reinstall of the Oracle client software (Oracle ODAC 10.2.2.21) fixed things. Twice before I had tried installing the 11g ODAC software and pointing to it and that changed nothing.
I don't have it off the top of my head but there is a lot of differences between these two binaries because 10g RC2 is about one gig while 11g is 1.7 gig. I also did not tell you Oracle it is best to use the correct client for each version except 8i which require 9i RC2 for most Microsoft development Oracle call not Microsoft.
Kind regards,
Gift Peddie
December 21, 2009 at 4:14 pm
Our servers are both 10g and 11g. The one I was testing against the most is 11g. So I thought the 11g client might help. Maybe if I had fully wiped out the old 10g install first. In either case, I'm still not understanding why a re-install would fix things.
December 21, 2009 at 4:40 pm
steves06 (12/21/2009)
Our servers are both 10g and 11g. The one I was testing against the most is 11g. So I thought the 11g client might help. Maybe if I had fully wiped out the old 10g install first. In either case, I'm still not understanding why a re-install would fix things.
I am assuming 10g connecting to SQL Server with 10g makes the difference because I have had connection issues to 9i with 10g client. I know your problem is different but in Oracle if it is not 8i I use version specific client.
Kind regards,
Gift Peddie
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply