Introduction

When working with sensitive data in SSIS, understanding and configuring the ProtectionLevel setting is essential for maintaining security throughout your ETL process.

---

How to check the ProtectionLevel setting

1. Open your SSIS project in SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS)
2. Right-click the project (or an individual package) and select Properties
3. In the Properties window, locate the ProtectionLevel option

This setting defines how sensitive information (like passwords or connection strings) is stored and secured.

---

Common ProtectionLevel options

• DontSaveSensitive Sensitive values are removed and not saved.
• EncryptSensitiveWithUserKey Data is encrypted using the current user’s credentials (not portable).
• EncryptSensitiveWithPassword Sensitive data is encrypted using a specified password.
• EncryptAllWithPassword The entire package is encrypted with a password.
• EncryptAllWithUserKey Entire package encrypted with the current user key (rarely used).
• ServerStorage Only used in SSISDB deployments; protection is handled by the server.

---

Best practices

• Use DontSaveSensitive for automated CI/CD pipelines or server deployments.
• Use EncryptSensitiveWithPassword when sharing packages between developers.
• Avoid user-based encryption (EncryptSensitiveWithUserKey) if you need portability.

---

Full tutorial

For a complete walkthrough with UI screenshots and additional tips:

👉 Read the full guide here