Tuesday, February 24, 2015

Change all the SSIS package's protection levels, all at once

So, I have a scenario where I'm working in SSIS 2012 on SQL Server 2012 with SQL Server Data Tools 2012 (SSDT), and in order to ensure all the packages are both coming to me and going to others with the same level of protection and level of editability, I need to change all of the protection levels of all the packages in my solution, all at once. Somehow, somewhere, one or more of them got reset to a non-default value - or maybe, we need to pull one back from some other source - either way, at anything above about two packages, this gets to be a real pain.

I found two options:

Before you do anything else:
  1. First things first, commit everything to source control, close SSDT, and make a zip backup of the whole project folder. 
  2. Verify the backup.
Then:

  1. Set or Change the Protection Level of Packages
    1. http://technet.microsoft.com/en-us/library/cc879310.aspx
    2. Now, open a command prompt at the folder with the packages you want to modify.
    3. Next, create either a command window, or a command file (AKA batch file) with this:
      1. for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;strongpassword
    4. What I wanted instead was to set them to DontSaveSensitive, plus, I have spaces in the file names (radical, I know), so I used this:
    5. for %f in (*.dtsx) do dtutil.exe /file "%f" /encrypt file;"%f";0 /Q
    6. The /Q doesn't prompt to overwrite the existing package
    7. You can find more about the DTUtil syntax here
      1. http://msdn.microsoft.com/en-us/library/ms162820.aspx
  2. Open the .dtproj file using your favorite text editor, and do a find and replace on some strings like
    1. me="ProtectionLevel">3

  • So, for me, it was 
  • me="ProtectionLevel">3
  • and replace with 
  • me="ProtectionLevel">0
  • last, reopen and build