Have a small SSIS package that connect to DB2 to extract a currency exchange rate.
I created a connection that needs a user id and password to connect to db2.
When I key in user name and password, and click Test Connection, it connects fine.
As soon as I click OK on new connection manager I created, the password disappears. When I try to execute the package, it fails saying the password is missing.
I also setup a DSN with the user name and password, but I still have to key in the password before I can press test connection. And again, as soon as I click OK in connection manager it disappears. Now the password remains in the DSN but not in the connection manager I created.
When I look at the properties of the Connection, the password is there but encrypted. But it is there.