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

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).