Why SSIS Packages Work in Visual Studio but Fail in Production (Sensitive Data, Deployment & Parameters)

Tips SSIS Tips
,
1

Applies To

  • SQL Server Integration Services (SSIS)
  • SSIS deployment models (Project & Package)
  • SSIS Catalog (SSISDB)
  • SQL Server Agent execution
  • Any SSIS package handling passwords, secret keys, tokens, and other sensitive values

Problem Summary

A common issue many SSIS developers run into is:

The package runs perfectly in Visual Studio, but fails after deployment with errors related to passwords, secret keys, or other sensitive data.

This usually happens when you execute the package via:

  • SSIS Catalog (SSISDB)
  • SQL Server Agent job
  • Command-line (DTExec)

Errors often include messages about missing or blank passwords, secret keys, or authentication failures, even though everything seemed correct during design-time preview.

What Causes This?

By default, SSIS packages use the EncryptSensitiveWithUserKey protection level.
This means:

  • Sensitive values (e.g., passwords, secret keys, refresh tokens) are encrypted using your Windows user profile when editing the package in Visual Studio.
  • At runtime (e.g., when the package is executed under a different account — SQL Agent, SSISDB, service account), SSIS may be unable to decrypt those sensitive values.
  • As a result, SSIS sees them as empty or invalid, leading to errors.

This behavior is explained in detail in the ZappySys KB article on running SSIS packages with sensitive data.

Why Deployment Matters

Starting with SQL Server 2012 and the Project Deployment Model:

  • SSIS packages are grouped inside a project (.ispac file) and deployed to the SSIS Catalog (SSISDB).
  • SSISDB supports Environment Variables and Project/Package Parameters, which help externalize configurations.
  • SSIS Catalog provides better versioning, centralized management, and monitoring.

However, without proper handling of sensitive values, even these modern deployment features won’t prevent decryption errors.

Recommended Solutions

1) Use SSIS Parameters for Sensitive Data

  • Instead of storing sensitive values inside connection managers or tasks, externalize them using SSIS parameters.
  • Parameters can be passed safely via:
    • SQL Server Agent job step
    • SSIS Catalog Environment variables

This ensures that sensitive values are provided at runtime and not tied to a specific user profile.

2) Set an Appropriate Protection Level

You should avoid the default EncryptSensitiveWithUserKey for production deployments.

Recommended options:

  • DontSaveSensitive – Does not store sensitive values in the package (best practice if using parameters)
  • EncryptSensitiveWithPassword – Encrypts sensitive values with a known package password

Both options allow you to avoid user-profile–specific encryption.

Step-by-Step: Best Practice Deployment Pattern

Step 1 — Create Project Parameters

  1. In Visual Studio, open your SSIS project
  2. Create project-level parameters for all passwords/secrets
  3. Mark them Sensitive = True if supported

Step 2 — Deploy to SSISDB

  1. Build the project to generate an .ispac file
  2. Deploy via:
  • Visual Studio Deployment Wizard
  • SQL Server Management Studio
  • Scripts
  1. In SSISDB, create Environments (e.g., Dev, Test, Prod)
  2. Define Environment Variables for sensitive values

See Simple Talk’s guide to SSIS Project Deployment for more details.

Step 3 — Map Environment Variables

  1. In SSISDB, configure the deployed project
  2. Reference the environment
  3. Map parameters to environment variables

This ensures correct values are applied when the package runs.

Step 4 — Execute the Package

  • Run via SQL Server Agent
  • Or run manually from SSIS Catalog
  • Ensure the correct environment is selected

Best Practices Checklist

:white_check_mark: Avoid storing sensitive values directly inside SSIS packages
:white_check_mark: Use parameters and environments for credentials and secrets
:white_check_mark: Choose a production-friendly ProtectionLevel (DontSaveSensitive or EncryptSensitiveWithPassword)
:white_check_mark: Use SSISDB for centralized execution and configuration

Summary

If a package works in Visual Studio but fails after deployment with errors around passwords, secret keys, or authentication:

  • It’s most likely due to user-key–encrypted sensitive data that can’t be decrypted under a different execution account.
  • The solution is to externalize sensitive values using parameters and SSISDB environments.
  • Update ProtectionLevel appropriately and provide secrets at runtime.

Helpful References

:link: How to run an SSIS package with sensitive data on SQL Server — ZappySys blog (sensitive data best practices) ZappySys Sensitive Data Guide
:link: SSIS 2012 Projects: Setup, Project Creation and Deployment — Simple Talk (deployment model overview) Simple Talk: SSIS Project Deployment
:link: Deploying Packages to SQL Server Integration Services Catalog (SSISDB) — SQLShack (deploying and executing packages) SQLShack: SSISDB Deployment Guide