title: How to deploy SSIS project with encrypted data?
body: <p>We have a SSIS project where one of the packages is connecting to a REST API. We use the HTTP connection manager (with username/password) and a script component to open the connection manager and parse the response. Protection level for all packages are EncryptSensitiveWithUserKey. Everything works in Visual Studio, and can be deployed with Deployment Wizard to the SSIS-DB. In the SSIS-DB we can run the package, and also change connection manager password/username via environments.</p>
  
  <p>But we are not able to achieve this via our normal automated deployment: Check-in to TFS and use VSTS-buildserver with Powershell scripts. When running the package from SSIS-db we get:</p>
  
  <blockquote>
    <p>Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.".
    You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.</p>
  </blockquote>
  
  <p>We (believe we) know how SSIS protection levels and encryption works, and the cause is obvious: The SSIS file is encrypted with user key, and the Deployment Wizard (run by developer!) decrypts/re-encrypts with the SSIS-catalog key. But the build server does not have the user key, hence the decryption-step is invalid.
  However, we would expect that this should not be an issue, since the password is replaced by the SSIS-environment, but is gives the above error.</p>
  
  <p>We have tried all protection levels:</p>
  
  <ul>
  <li>DontSaveSensitive: Package can't run in either VS/SSISDB.</li>
  <li>EncryptSensitiveWithPassword: Passwords are unsupported in the PowerShell $folder.DeployProject command. <a href="https://www.mattmasson.com/2012/06/publish-to-ssis-catalog-using-powershell/" rel="nofollow noreferrer" title="as here">Same method as here.</a></li>
  </ul>