SSIS tutorial: Package protection level encryption options

SSIS Tutorials
,
1

Introduction

Protecting sensitive data in SQL Server Integration Services (SSIS) packages is essential, especially when packages contain connection strings, passwords, or other confidential information. SSIS offers multiple protection levels to encrypt either sensitive data or the entire package. This tutorial explains each option and demonstrates how to configure an SSIS package using the ZappySys SFTP Connection as an example, ensuring secure handling of sensitive information. However, you could apply it to any SSIS connector.

Prerequisites

Steps

Configure the SFTP Connection

  1. Drag and drop the Secure FTP Task into the Control Flow and open the task.
  2. Select the Action you want to perform; for this tutorial, we will use Delete FTP file(s).
    Delete action example
    Delete action example690×501 27.6 KB
  3. Specify the file or files you want to delete.
  4. Under Connection, click New Connection to configure the SFTP connection.
  5. Enter the server’s host, port, username, and password. Mark the password as sensitive to ensure SSIS encrypts it according to the package ProtectionLevel.
  6. Test the connection and click OK to save the configuration.
    SFTP connection
    SFTP connection568×655 26.6 KB
  7. Click OK to close the task configuration.

Setting the Protection Level

  1. Open SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS).
  2. Locate the SSIS project in Solution Explorer or Object Explorer.
  3. Right-click the project and select Properties.
    Project Properties
    Project Properties386×652 19.2 KB
  4. In the Project section, under Security, set ProtectionLevel to the desired option (e.g., EncryptAllWithPassword).
  5. If you select a password-based protection level, provide the password and confirm it.
    ProtectionLevel
    ProtectionLevel802×546 27.5 KB
  6. Click OK to save the project configuration.
  7. You may see the following message:
The ProtectionLevel property of the project has changed.
To build the project, the packages need to have the same protection level as the project.
Open each package and modify the ProtectionLevel property.
  1. Open each package, go to the Properties window, and under Security, select the same protection level used for the project.
  2. Enter the password (if applicable) and confirm it.
  3. Save the package configuration.
    Package Properties
    Package Properties426×779 28.1 KB

Using Command Prompt with dtutil

You can also configure the package ProtectionLevel via the command line:

Set EncryptSensitiveWithPassword (level 2) with a password:

dtutil.exe /file "C:\Packages\MyPackage.dtsx" /encrypt file;"C:\Packages\MyPackage.dtsx";2;strongpassword

For multiple packages in a folder:

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

Use %%f instead of %f when running the command in a batch file.

Definition of Sensitive Information

SSIS considers the following as sensitive:

  • Passwords in connection strings.
  • Task-generated XML nodes flagged as sensitive by SSIS.
  • Variables explicitly marked as sensitive.

SSIS determines sensitivity based on the component (connection manager or task) definition. Users cannot add or remove properties from the sensitive list.

Package Encryption

SSIS uses the Microsoft Data Protection API (DPAPI) to encrypt package data. Protection levels can encrypt either sensitive properties only or the entire package. Password-based encryption uses AES 256-bit (AES for SQL Server 2017+; Triple DES 192-bit in SSIS 2016).

Protection Levels

  • DontSaveSensitive: Suppresses sensitive property values when the package is saved. No encryption is applied. Other users must manually enter sensitive information.
  • EncryptSensitiveWithUserKey: Encrypts only sensitive properties using the current user profile. Only the same user can open or run the package; others see blanks.
  • EncryptSensitiveWithPassword: Encrypts only sensitive properties using a password. Users must enter the password to open or execute the package.
  • EncryptAllWithUserKey: Encrypts the entire package using the current user profile. Only the same user can open or execute the package.
  • EncryptAllWithPassword: Encrypts the entire package using a password. Users must enter the password to open or run the package.
  • ServerStorage: Protects packages using SQL Server database roles. Used by the SSISDB catalog. Not supported for file system packages from SSDT.

Package Lifecycle and Protection Level Recommendations

  1. During Development:

    • Default: EncryptSensitiveWithUserKey
    • Ensures only the developer sees sensitive data.

  2. Before Deployment:

    • Change to password-based (EncryptSensitiveWithPassword) to allow the operations team access.
    • Provide a temporary strong password known to the team.

  3. After Deployment:

    • Operations team can re-encrypt packages using a unique password or user key tied to the execution account.

Conclusion

SSIS ProtectionLevel is essential for securing sensitive data in packages. Understanding each protection option allows you to control access effectively, ensure safe handling of credentials, and prevent unauthorized access during the package lifecycle.

Explore our SSIS PowerPack for more information and download and install it to start building smarter, faster, and more scalable solutions.

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.

2

Great, it worked for me