When working with SQL Server Integration Services (SSIS), you may come across a property called ProtectionLevel. This property is used to specify how sensitive information is saved within the SSIS package and whether to encrypt the package or the sensitive portions of the package. In this article, we will explore the available options for the ProtectionLevel property and provide examples of how they can be used.

DontSaveSensitive

The DontSaveSensitive option ensures that any sensitive information is not written out to the package XML file when you save the package. This can be useful when you want to exclude sensitive information before sharing the package with others. When you open the package, the sensitive properties will be blank, even if the “Save my password” checkbox is checked.

EncryptSensitiveWithUserKey

The EncryptSensitiveWithUserKey option encrypts sensitive information based on the credentials of the user who created the package. The sensitive information is stored in the package XML, but it cannot be decrypted by anyone other than the user who created the package. If another user tries to open the package, they will encounter an error and the sensitive properties will be blank.

EncryptSensitiveWithPassword

The EncryptSensitiveWithPassword option requires you to specify a password in the package. This password is used to encrypt and decrypt the sensitive information in the package. When opening a package with this protection level, you will be prompted to enter the password. This allows any user with the password to open and execute the package.

EncryptAllWithPassword

The EncryptAllWithPassword option allows you to encrypt the entire contents of the SSIS package with a specified password. This includes both sensitive and non-sensitive information. When opening the package, you will be prompted to enter the password. It is important to note that if you lose the password, there is no way to retrieve the package contents.

EncryptAllWithUserKey

The EncryptAllWithUserKey option encrypts the entire contents of the SSIS package using the user key. This means that only the user who created the package will be able to open, view, modify, and execute it. Other users will not have access to the package contents.

ServerStorage

The ServerStorage option allows the package to retain all sensitive information when saving it to SQL Server. This option assumes that the MSDB database, where the package is saved, is adequately secured. Sensitive information is stored in an unencrypted form within the package.

Understanding the different ProtectionLevel options in SSIS packages is crucial for managing sensitive information and ensuring the security of your data. By selecting the appropriate ProtectionLevel, you can control who can access and view the sensitive information within your SSIS packages.