How to change the default Package Protection Level in SSIS?

Question

When I'm creating a new SSIS package in the SQL Server Business Intelligence Development Studio, the default ProtectionLevel is EncryptSensitiveWithUserKey, but the standard ProtectionLevel we want to use is DontSaveSensitive.

Does anyone know if there is a way to change the default ProtectionLevel value?

Answer 1

2012 users rejoice

SSIS 2012 allows for project-level setting of package protection level.

If I manually change the package protection level to something different from the project, it will fail when you attempt to run/build.

Project consistency check failed. The following inconsistencies were detected: Package3.dtsx has a different ProtectionLevel than the project.

So what's a soul to do with 2005-2008R2?

I have found the best approach is to create template packages, either in a project or in the actual visual studio install.

After creating your base package, setting up logging, configuration, package protection level, etc, you then need to find a way to have people use it. If it's project-based, I find it is important to have people regenerate package IDs after copying the package. BIDSHelper is wonderful for addressing this-simply right click on the package in the solution explorer and select Reset GUIDs. Otherwise, I have an annotation in my base package reminding developers to right click on the control flow, select properties and in the ID property, click <Generate New ID>

If you choose to go establish templates from the "Add, New Item menu" (ctrl-shift-A) all you need to do is copy the package(s) into your template folder.

Answer 2

click anywhere in the design area of the Control Flow tab in the SSIS designer to show the package properties and choose according to your requirement in ProtectionLevel of Security TreeView.....

Answer 3

In my experience changing the project's properties level does nothing to the individual packages. But there is a solution in the following page:

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

Which is to use a command like so..

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

in your SSIS project folder. 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