THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Understanding the SSIS Package Protection Level

One property of all SSIS packages that you must understand is the ProtectionLevel. This property tells SSIS how to handle sensitive information stored within your packages. Most commonly this is a password stored in a connection string. Why is this information important? If you don’t set the ProtectionLevel correctly, the package may become unusable. Other developers may be unable to open the package or the package may fail when you go to execute it. Understanding these options lets you get out in front of possible problems and will help you to fix an issue if a problem crops up. In a perfect world, you would not need to store sensitive data, but each and every environment is different. Let’s look at each of the ProtectionLevel options.


When the package is saved, sensitive values will be removed. This will result in passwords needing to be supplied to the package, through a configuration file or by the user.


This will encrypt all sensitive data on the package with a key based on the current user profile. This sensitive data can only be opened by the user that saved it. It another user opens the package, all sensitive information will be replaced with blanks. This is often a problem when a package is sent to another user to work on.


Sensitive data will be saved in the package and encrypted with a supplied password. Every time the package is opened in the designer, you will need to supply the password in order to retrieve the sensitive information. If you cancel the password prompt, you will be able to open the package but all sensitive data will be replaced with blanks. This works well if a package will be edited by multiple users.


This works the same as EncryptSensitiveWithPassword except that the whole package will be encrypted with the supplied password. When opening the package in the designer, you will need to specify the password or you won’t be able to view any part of the package.


This works the same as EncryptSensitiveWithUserKey except that the whole package will be encrypted. Only the user that created the package will be allowed to open the package.


This option will use SQL Server database roles to encrypt information. This will only work if the package is saved to an SSIS server for execution.

So that’s it. This option is pretty basic but it is important to understand so that you can be spared unnecessary frustration.

Published Tuesday, January 12, 2010 11:46 AM by ejohnson2010
Filed under: ,



Julian said:

Can you provide any further imformation about the ServerStorage option?

June 4, 2010 6:16 AM

Brian said:

Nice summary. This has been really helpful. Thanks Eric

October 1, 2012 5:36 PM

Phillip said:

Thank you for the information. Now I understand much more about SSIS Package Protection Levels.

January 9, 2013 6:52 AM

rupal said:

nice article, curious would this SSIS property setting apply to third party connectors (i.e. Attunity) which appear to not save password to when another user opens/runs the package? I basically, want to persist the passwd regards who opens/runs the job.  

April 15, 2013 7:24 PM

Leah said:

Thanks for the clear and detailed explanation.

March 4, 2014 2:22 AM

Judy said:

Thanks, This helped me today !

March 21, 2014 11:49 AM

vanita said:

Thanks Eric it helped a lpt

August 8, 2014 4:25 PM

Ram kumar tiwary said:

Thanks this is very helpful for me.:)

February 26, 2015 6:55 AM

Wounded Ego said:

Thank you for this post. I have a question that hopefully someone can help me with.

Should the protectionlevel have any effect on running the package interactively? For some reason when I have it set to "do not save sensitive" it ignores the password even though it is provided.


April 8, 2015 9:42 AM

Hasiya said:

Many thanks. I found this article very useful, I understand much more about SSIS Package Protection Levels.

September 17, 2015 3:52 AM

suebeen said:

Thank you Eric. Who/what defines the sensitive data and where is this specified?

September 23, 2015 5:26 PM


very nice,

October 8, 2015 1:41 AM

santhosh said:

nice article...

October 14, 2015 10:19 AM

Ali said:

Great Information. Very helpfull

November 5, 2015 4:57 PM

squantrill said:

So basically Microsoft hasn't built an option so that another user can run the package easily. CLAP CLAP CLAP CLAP, another notch in the belt for Microsoft wizards.

November 13, 2015 8:23 AM

Zinc said:

How does a CM build script build a package that's protected by a password that has to be entered when it opens it?  Or is secured to a developer user that isn't the build user?  Could this be any more brain damaged?   This renders SSIS packages useless unless you hack around this problem by manually stuffing the password in via scripting.   Security that is too inflexible and has to be circumvented is not security at all.

November 20, 2015 10:07 AM

pavan kora said:

very good information no one is provided like this.thank you dear.

December 11, 2015 2:25 PM

Sondra Servais said:

How do I check the protection level on a package stored in Integration Services database?

April 13, 2016 11:04 AM
New Comments to this post are disabled

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Privacy Statement