I prefer to keep our SSIS packages in a solution on the server, not in sql. By default, sensitive data is encrypted with a user key. Since the sql server agent uses a service account to run jobs, we have to change this encryption method to something else. I like encrypting with a password.
The problem is whenever I setup a job in a step there is no place to input this password. When I click on the configurations tab, I get a popup dialog for the password. That keeps it stored properly, but is this really the right place to put it. It seems really unusual, and I keep running into issues where it seems to reset itself if I make certain changes.
Does anyone know of a better place to input this password that is more stable?
Thanks,
There is no need for you to have a password in the package at all if you can use Windows authentication and avoid SQL Server authentication and ensure that the rights necessary to execute your package are available to your service account.
Barring that, you should already be overriding the connection string with a config file using package configurations so your package is portable, and then the password doesn't need to be in the dtsx anyway.