title: Password trouble in SSIS using Oracle Provider for OLE DB
body: <p>This is my first time trying to extract data from an Oracle database and push it into a Microsoft SQL database, and I'm running into an issue I cannot find a way around. I've installed ODAC, Oracle Client, on the SSIS machine and am able to see and use the Oracle Provider for OLE DB Connection manager just fine. I put in the Oracle server name, login, PW, test the connection, works fine. I can even run the SOURCE query and load it into our SQL database just fine. But if I try and deploy the package, or open up the connection manager again, everything fails because the password isn't saved, despite the obvious "save password" checkbox being checked.</p>
  
  <p>After some searching, it appears that checkbox just doesn't do anything, and I've tried the following workarounds with no success:</p>
  
  <ul>
  <li>Configuration Files. Set up the package to use a configuration file, tried to hard-code the password into that, both on its own and/or included in the connection string line. The package just ignores the password in either case.</li>
  <li>Expressions. I've tried using the expression in this format: "Data source=SERVER;"+ "user id = USERID; " + "password = PASSWORD; " + "Provider = OraOLEDBOracle.1; " + "persisit security info = true;", which fails (connection manager always switches to "offline" mode and doesn't seem to process the expression), and also tried to just use the expression for "password" which... sort of worked one time, inside SSIS (it seems to run and looks OK inside the data flow but on the control flow the Connection Manager shows as offline again) but fails when deployed.</li>
  <li>In all cases, I've tried using various ProtectionLevel settings: DontSaveSensitive, EncryptSensitiveWithUserKey, EncryptSensitiveWithPassword (and then adding the password to the execution of the package in the SSMS job manager). None have worked any different than the others.</li>
  </ul>
  
  <p>I've tried this both on our actual server where our SQL DBs and SSIS server live, and on my local machine to test out the same settings to ensure it's not machine-specific. I've uninstalled and reinstalled Oracle and the ODAC on both at least once now. </p>
  
  <p>At this point I can't find any other suggestions, it seems like one of those setups has worked for everyone eventually after installing everything properly. And again, the package WORKS as long as I set the password IN the connection manager and run the package manually inside SSIS, but not under any circumstances outside SSIS. </p>
  
  <p>I've spent more than 2 straight days trying to troubleshoot this and am beginning to lose my mind. Aside from the obvious complaint of "What the hell is that save password checkbox even for then" I dunno what to try at this point. I really really want to use the Oracle Provider because some of this data is using complicated queries and even the smallest one is 200K+ rows, and hitting the corporate "warehouse" so it's essential to be as efficient as possible. If there's no other alternative I could try the older OLE DB Connections but that's an absolute last resort at this point.</p>
  
  <p>Other info I can think of: Using Visual Studio 2010 (tried both free and professional versions), tried running both 32bit and 64bit runtime on packages (runs fine either way in SSIS but neither way in SSMS because... no password), and I have no control over the Oracle server/DBs and there's 0 chance of getting a no-password account for access (nor should I need to frankly)</p>
  
  <p>Thank you for any assistance or tips!</p>