How to parameterize properties in SSIS package and configure them in a SQL job

Introduction

In this article, we will cover the steps of how to run an SSIS package with sensitive data on SQL Server.

In an SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So if ProtectionLevel of a package is set to EncryptSensitiveWithUserKey (or DontSaveSensitive), SSIS will fail to decrypt tokens/passwords, when you run it from another machine using another Windows account. To circumvent this, when you are creating an SSIS package that uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, or any other component that uses sensitive data), consider using parameters/variables to pass tokens/passwords to the package. In this way, you won’t face authentication-related errors when the package is deployed to a production server. Below you will find out the ways how to parameterize and configure parameters when running an SSIS package in a SQL Job.

Is it necessary to parameterize all the authentication details/credentials?

No. You just need to parameterize those properties that are needed in the process of authentication or making a request. For example, if you are using OAuth Connection Manager, you will need to parameterize RefreshToken, and ClientSecret in the Authorization Code Grant (3-Legged Auth) authentication scenario. If you use Password Grant, you will need to parameterize the Password property as well. You do not need to parameterize AccessToken, because it is generated using a RefreshToken.

But you may consider parameterizing environment-specific properties such as URL, ClientID, UserName, and similar even if they are not encrypted by SSIS. You would need to do that when you work in one environment when developing and testing packages while in another in production, UAT, etc. For example, you would use one OAuth Application while developing but another in production — you would also need to parameterize ClientID and perhaps Authorize and Token URLs too.

How to parameterize an SSIS Connection Manager

Most often sensitive data is used by SSIS Connection Manager, in password, OAuth token, etc. properties. To pass a token or a password dynamically you will have to use a variable/parameter together with expression. 2012 and above versions of Visual Studio or BIDS have a nice way to do it, while in Visual Studio 2008 you will have to set it up manually. Another way is to set sensitive data using SSIS Package Configuration file, which is applicable when you have enabled package deployment model (obsolete method).

Using parameters or variables

Below you will learn how to set a property using a variable/parameter together with an expression. If you are not sure, what property to set, check the documentation of connection managers to find out.

Using a parameter

1. Right click on a Connection Manager and press Parameterize:
   

   Connection Manager parameterization

2. Then select an appropriate property you want to parametrize, e.g. Password, and give a name to the parameter. For OAuth Connection Manager we will use  RefreshToken.
   

   Parameterizing OAuth Connection Manager property “RefreshToken”

Using a variable

Below is an example how to set any property of a Data Flow using an expression and a variable. You can do the same thing for a Connection Manager – just create a variable, find a property named Password or RefreshToken, etc. and use that variable to set its value:

Using a configuration file (obsolete method)

So if you wanted to parameterize RefreshToken of OAuth Connection Manager, SSIS Package Configuration file would look like this:

How to pass a variable/parameter to an SSIS Connection Manager

Executing a package from SSIS Catalog

When you are executing a package from SSIS Catalog to set a property you will have to use a parameter.

1. Select SQL Server Agent Job and access its properties. Then select SSIS Catalog as Package source and select your package:
   

   Running SSIS package from SSIS Catalog

2. In Configuration tab parameter will be already present, so just click on [...] button and input your parameter value:
   
   

   Configuring parameter for SSIS package which is run from SSIS Catalog

Using a parameter from SSIS Environment

Another way of passing a parameter when using SSIS Catalog is to use Environment parameter.

1. Create an environment if you don’t have one already and access its properties:
   

   Creating and configuring SSIS Environment

2. Click Variables and create a parameter with appropriate type and don’t forget to check Sensitive checkbox:
   

   Creating a sensitive parameter in SSIS environment

3. Then go to your SSIS Catalog project and click Configure:
   
4. Click References and Add button. Select the environment you had created in first step:
   

   Adding SSIS environment to SSIS Catalog project

5. Then click Parameters and  [...]  button on the parameter you want to set:
   

   Setting an environment variable for SSIS Catalog project

6. The result should look similar to this:
   

   Result after you configured SSIS Catalog project to use an environment variable

7. Then in SQL Server Agent Job properties, Configuration tab select the environment you created. Configured parameter will be set automatically:
   

   Using environment variable in SQL Server Agent Job

SSIS Sensitive Data Issue with OAuth Authentication

When working with OAuth-based connectors in SSIS (such as Dynamics CRM, Dataverse, or other Azure AD–secured APIs), it’s common to store credentials like Client ID and Client Secret inside SSIS variables or parameters.

However, incorrect handling of sensitive data in SSIS can cause SQL Server Agent job failures, even when the package runs successfully in Visual Studio (SSDT).

SSIS Package Works in Visual Studio but Fails in SQL Server Agent

A common scenario occurs when an SSIS package executes without issues in Visual Studio, but fails when scheduled or executed via SQL Server Agent.

In one real-world case, a Dynamics CRM connector was configured using OAuth authentication, and the following values were stored in SSISDB parameters:

• Application (Client) ID
• Client Secret

When both parameters were marked as Sensitive, the package failed during SQL Agent execution, despite working correctly at design time.

Why Marking SSIS Variables as Sensitive Causes Job Failures

This behavior is not specific to ZappySys; it is a well-known SSIS limitation:

• SSIS encrypts values marked as Sensitive
• Decryption depends on the execution context (user vs SQL Agent service account)
• Over-encrypting non-secret values can prevent successful runtime decryption

This issue frequently appears when non-secret OAuth parameters are incorrectly marked as sensitive.

How to Store OAuth Client ID and Client Secret in SSIS Securely

For OAuth-based authentication in SSIS (Dynamics CRM, Dataverse, Azure AD APIs, etc.), use the following recommended configuration:

Only true secrets should be marked as Sensitive. The Client ID is not confidential and does not require encryption.

Best Practice: Use SSISDB Sensitive Parameters Instead of SSIS Variables

To avoid SQL Agent execution issues, always use SSIS Catalog (SSISDB) parameters for sensitive values:

1. Deploy the package to SSISDB
2. Convert connection properties into Project or Package Parameters
3. Mark only sensitive values (such as Client Secret) as Sensitive
4. Assign values through SSISDB Environments

SSISDB encrypts sensitive parameters securely and allows SQL Server Agent to decrypt them correctly at runtime.

For detailed steps, refer to the official documentation:

SSIS Parameterization Guide for ZappySys Connectors

Common Mistakes When Handling Sensitive Data in SSIS

• Marking Client ID or Tenant ID as Sensitive
• Using SSIS variables marked as Sensitive instead of SSISDB parameters
• Assuming all OAuth fields require encryption

SSIS Sensitive Data and SQL Agent Execution: Key Takeaways

• SQL Server Agent failures often occur due to incorrect Sensitive parameter configuration
• OAuth connectors typically require only the Client Secret to be protected
• Proper SSISDB parameterization ensures reliable scheduled execution

Pro Tip: If your SSIS package runs in Visual Studio but fails in SQL Server Agent,
review which parameters are marked as Sensitive before troubleshooting the connector itself.

Executing a package from a File system / SSIS Package Store / SQL Server

When you are executing a package from a File system / SSIS Package Store or SQL Server you will have to use a variable (parameters won’t work).

Using variables in SQL Server Agent Job configuration

You can pass a variable in SQL Server Agent Job properties in Set values tab:

Using a variable in SQL Server Agent Job

Using a configuration file in SQL Server Agent Job configuration

You can pass configuration file in SQL Server Agent Job properties in Configurations tab:

Using a package configuration file in SQL Server Agent Job

Resources

• Microsoft article about Package Protection Level.
• Using sensitive parameters (SSIS series).