I'm developing SSIS project locally using project deployment model. I use SQL Server Authentication to connect to remote server. During this period Protection level is set to EncryptSensitiveWithUserKey.
However, upon deployment this package will be executed in a completely different environment, using SSPI security context and with environment variables from SSIS catalog.
This means that right now, before deployment I have to manually change Protection level to DontSaveSensitive inside a project and every package in it. Only after that tedious procedure I deploy the project to remote server.
I cannot keep DontSaveSensitive locally because every time I run a package it will ask me for a password. I cannot deploy with EncryptSensitiveWithUserKey either, because it gives error saying it cannot decrypt some of the values (passwords).
My question is how can skip changes of protection level manually every time I deploy a package? How can I automate this?
Best Answer
Since you're in project deployment mode, which means you are deploying to the SSISDB, there's no need for what you're doing ;)
When you deploy, the isdeploymentwizard.exe is handling all that for your
As part of the deployment, the "Changing protection level" step switches the packages to ServerStorage level