Expired Passwords and the Oracle Provider

I ran into a problem a while back that caused me a lot of grief so I thought I’d post it here. The package in question was used to traverse hundreds of Oracle databases and run the same query on all of them. This data was then loaded into a data mart. The databases all had an Oracle user on them with a predetermined username and password. It was also in an environment where Oracle Internet Directory (OID) was in use. So it was fairly straightforward to use an SSIS Expression to build the connection string dynamically as the only thing that had to change was the Oracle SID. The connection string looked like this:

    “Provider=OraOLEDB.Oracle;User ID=DBUser;Password=DBPassword;Data Source=OracleSID;”

The package worked flawlessly in development. But when it was deployed to production, it would hang on connections to specific servers. After beating my head against the wall for a while, I discovered that on every server it would fail on, the password was expired for the account. It turns out that by default, the Oracle provider will pop a dialog box for an expired password, prompting a password change. In something batch based like SSIS, this results in the process hanging. Adding a switch to the connection string to suppress the dialog box did the trick.

    “Provider=OraOLEDB.Oracle;User ID=DBUser;Password=DBPassword;Data Source=OracleSID;PwdChgDlg=0;”

This strikes me as something that should default to 0 but in any case it’s one of those little things that is not a big deal until it happens to you and can be tough to track down.