Change protection level in multiple SSIS packages at once.
If you are working on packages with multiple developers it is usefull to set the protectionlevel of a project and the packages to Protection Level 2 (Secure sensitive data with password). By default it is set to Level 1 (Secure sensitive data with user key), this clears the sensitive data if another user opens the package. When using level 2 other users can enter the password and keep sensitive data in the package intact.
The following steps need to be done to alter all packages at once:
- If you are using Source Controle make sure you check out the packages so you can edit the files.
- Close the Solution
- Navigate to you workspace directory where the dtsx files are stored that you need to change.
- Open a command prompt at the folder with the packages you want to modify
- Use the following command to set the property:
- for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;{strongpassword} /Q
- The addition of /Q makes sure that it doesn’t prompt to overwrite the existing package
- replace {strongpassword} with an actual password
- The number needs to be changes to the level you want to set. (0,1,2,3)
- for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;{strongpassword} /Q
- Open de .dtproj file in a text editor and Find & Replace the string:
- Name=”ProtectionLevel”>1
- Replace with: Name=”ProtectionLevel”>2
- The number at the end of the string depends on the current and target level (0,1,2,3)
- Replace with: Name=”ProtectionLevel”>2
- Name=”ProtectionLevel”>1
- Open the Solution again
- Change the protection level of the project to the same level as the packages.
- Build the Solution
- You are done!