{"id":1364,"date":"2017-11-08T18:16:40","date_gmt":"2017-11-08T18:16:40","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1364"},"modified":"2026-02-04T10:35:39","modified_gmt":"2026-02-04T10:35:39","slug":"how-to-run-an-ssis-package-with-sensitive-data-on-sql-server","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/how-to-run-an-ssis-package-with-sensitive-data-on-sql-server\/","title":{"rendered":"How to parameterize properties in SSIS package and configure them in a SQL job"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"\"In this article, we will cover the steps of how to run an SSIS package with sensitive data on SQL Server.<\/p>\n

In an SSIS package\u00a0sensitive data such as tokens and passwords are by default encrypted by SSIS<\/a>\u00a0with your Windows account which you use to create a package. So if ProtectionLevel<\/em> of a package is set to EncryptSensitiveWithUserKey<\/em> (or DontSaveSensitive<\/em>), 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\u00a0OAuth Connection Manager<\/a>\u00a0or an\u00a0HTTP Connection Manager<\/a>\u00a0with 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\u2019t 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.<\/p>\n

Is it necessary to parameterize all the authentication details\/credentials?<\/h2>\n

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<\/em>, and ClientSecret\u00a0<\/em>in the Authorization Code Grant (3-Legged Auth) authentication scenario. If you use Password Grant, you will need to parameterize the Password<\/em> property as well. You do not<\/em><\/span> need to parameterize AccessToken<\/em>, because it is generated using a\u00a0RefreshToken.<\/em><\/p>\n

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.<\/p>\n

How to parameterize an SSIS Connection Manager<\/h2>\n

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<\/em> file, which is applicable when you have enabled package deployment model<\/a>\u00a0(obsolete method).<\/p>\n

Using parameters or\u00a0variables<\/h3>\n

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<\/a> to find out.<\/p>\n

Using a parameter<\/h4>\n
    \n
  1. Right click on a Connection Manager and press Parameterize<\/em>:\n
    \"Connection

    Connection Manager parameterization<\/p><\/div><\/li>\n

  2. Then select an appropriate property you want to parametrize, e.g. Password<\/span>, and give a name to the parameter. For OAuth Connection Manager<\/a> we will use\u00a0RefreshToken<\/span>.\n
    \"Parameterizing<\/a>

    Parameterizing OAuth Connection Manager property “RefreshToken”<\/p><\/div><\/li>\n<\/ol>\n

    NOTE:\u00a0<\/strong>Applies to SQL Server 2012 and above.<\/div><\/div>\n

    Using a variable<\/h4>\n

    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<\/span>\u00a0or RefreshToken<\/span>, etc. and use that variable to set its value:<\/p>\n

      \r\n \t
    1. To define an expression for any property of a component, first navigate to the Data Flow designer surface.\"SSIS<\/a><\/li>\r\n \t
    2. Now, right-click anywhere in the data flow designer surface and click the \"Properties\" menu item.<\/li>\r\n \t
    3. When the Properties window appears, select a property and press the [...] button to set an expression.<\/li>\r\n \t
    4. Now enter the property and expression you need.\"SSIS<\/a><\/li>\r\n<\/ol><\/div>\n

      Using a configuration file (obsolete method)<\/h3>\n

      So if you wanted to parameterize RefreshToken<\/span>\u00a0of OAuth Connection Manager<\/a>, SSIS Package Configuration file would look like this:<\/p>\n

      <?xml version=\"1.0\"?>\r\n<DTSConfiguration>\r\n    <DTSConfigurationHeading>\r\n        <DTSConfigurationFileInfo GeneratedBy=\"ZappySys\\ZappySys\" GeneratedFromPackageName=\"-Sandbox-\" GeneratedFromPackageID=\"{152417C7-3764-4910-91E3-1D35C8FE89AE}\" GeneratedDate=\"2017-11-03 19:22:13\"\/>\r\n    <\/DTSConfigurationHeading>\r\n    <Configuration ConfiguredType=\"Property\" Path=\"\\Package.Connections[OAuth 1].Properties[RefreshToken]\" ValueType=\"String\">\r\n        <ConfiguredValue>real-refresh-token-value-goes-here<\/ConfiguredValue>\r\n    <\/Configuration>\r\n<\/DTSConfiguration><\/pre>\n
      How to pass a variable\/parameter to an SSIS Connection Manager<\/h2>\n
      Executing a package from SSIS Catalog<\/h3>\n
      When you are executing a package from SSIS Catalog to set a property you will have to use a parameter<\/em>.<\/p>\n
        \n
      1. Select SQL Server Agent Job<\/em> and access its properties. Then select SSIS Catalog<\/span>\u00a0as Package source<\/em> and select your package:\n
      2. In Configuration<\/em> tab parameter will be already present, so just click on […]<\/span>\u00a0button and input your parameter value:
        \n<\/a><\/p>\n
        \"Configuring<\/a>
        Configuring parameter for SSIS package which is run from SSIS Catalog<\/p><\/div><\/li>\n<\/ol>\n
        NOTE:\u00a0<\/strong>Applies to SQL Server 2012 and above.<\/div><\/div>\n
        Using a parameter from SSIS Environment<\/h4>\n
        Another way of passing a parameter when using SSIS Catalog is to use Environment<\/em> parameter.<\/p>\n
          \n
        1. Create an environment if you don’t have one already and access its properties:\n
        2. Click Variables<\/em> and create a parameter with appropriate type and don’t forget to check Sensitive checkbox<\/span>:\n
        3. Then go to your SSIS Catalog project and click Configure<\/span>:
          \n          \"\"<\/a><\/li>\n
        4. Click References<\/span>\u00a0and Add<\/span>\u00a0button. Select the environment you had created in first step:\n
        5. Then click Parameters<\/span>\u00a0and\u00a0 […]<\/span>\u00a0 button on the parameter you want to set:\n
        6. The result should look similar to this:\n
        7. Then in SQL Server Agent Job<\/em>\u00a0properties, Configuration<\/span>\u00a0tab select the environment you created. Configured parameter will be set automatically:\n
          \"Using<\/a>
          Using environment variable in SQL Server Agent Job<\/p><\/div><\/li>\n<\/ol>\n
          SSIS Sensitive Data Issue with OAuth Authentication<\/h2>\n
          When working with OAuth-based connectors in SSIS (such as Dynamics CRM, Dataverse, or other Azure AD\u2013secured APIs), it\u2019s common to store credentials like Client ID<\/strong> and Client Secret<\/strong> inside SSIS variables or parameters.<\/p>\n
          However, incorrect handling of sensitive data in SSIS can cause SQL Server Agent job failures<\/strong>, even when the package runs successfully in Visual Studio (SSDT). <\/p>\n
          SSIS Package Works in Visual Studio but Fails in SQL Server Agent<\/h3>\n
          A common scenario occurs when an SSIS package executes without issues in Visual Studio, but fails when scheduled or executed via SQL Server Agent<\/strong>. <\/p>\n
          In one real-world case, a Dynamics CRM connector was configured using OAuth authentication, and the following values were stored in SSISDB parameters:\n<\/p>\n