Protection level changed mid project - now project won't build

Question

Started a new SSIS project, and forgot to set the default protection level to "Don't save sensitive" (our standard) Now midway through the project and made the changes (at project level and in each package.) When checked all packages are Don't Save Sensitive and the project is Don't Save Sensitive, however when attempting a build, I get

Project consistency check failed. The following inconsistencies were detected: PACKAGE1.dtsx has a different ProtectionLevel than the project. PACKAGE2.dtsx has a different ProtectionLevel than the project. ... PACKAGE(N).dtsx has a different ProtectionLevel than the project.

(it lists every package in the project even though they all match the Project level protection.)

Answer 1

I suspect you ran into the same issue I did. I corrected all my packages via the API so that they all indicated they were DTS:ProtectionLevel="0" which is unprotected.

The project (.dtproj) file also has a protection level which gets set to DontSaveSensitive. <SSIS:Project SSIS:ProtectionLevel="DontSaveSensitive" xmlns:SSIS="www.microsoft.com/SqlServer/SSIS">

The mismatch for me was that inside the project file, it keeps track of far too much information about each package so if you scroll down, you'll see an entry per package like <SSIS:Property SSIS:Name="ProtectionLevel">3</SSIS:Property> or whatever the default number is. Make that 0 in the file (search and replace). Save the project file and it'll now build.

You might need to perform a Build All to get it to build. I suspect that VS/SSDT is trying to use the extra data it stores in the .dtproj file to determine whether it needs to validate all the packages in a project. Since we hand edited the file, it didn't trip whatever sensor would normally be flipped to signal a full recompile was needed.

Answer 2

billinkc's answer didn't work for me because changing the value with a text editor doesn't change it correctly. The following MSDN page explains that there is a cmd line tool to manage this:

http://technet.microsoft.com/en-us/library/cc879310.aspx

Like so:

for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;strongpassword

It will change every module in the project to the protection level specified in the second to last value. If it is 0, don't store values, then you don't need the password and can get rid of the last semicolon and everything after.

The following MSDN article has a table with the numbers for each protection level, as used with dtutil:

http://technet.microsoft.com/en-us/library/ms141747.aspx