SSIS Tutorial: How to use expressions inside API connection

Tutorials SSIS Tutorials
,
1

Introduction

API Connection Managers support SSIS Expressions, allowing you to dynamically change connection settings at runtime without manually editing the connection.

This is especially useful when deploying packages across multiple environments (Development, QA, Production), switching between different accounts or API instances, or storing sensitive information in SSIS variables instead of hardcoding it.

Most API providers share a common set of properties, such as:

  • ClientId
  • ClientSecret
  • UserName
  • Password
  • ServiceUrl

However, each provider also exposes its own provider-specific settings, such as:

  • Scope
  • SiteId
  • SpreadSheetId
  • TenantId
  • DriveId
  • BucketName
  • WorkspaceId
  • API Version
  • and many others.

Prerequisites

Steps

Configure the API Connection

  1. Create a new API Connection Manager.
  2. Select the desired API Provider.
  3. Fill in all required connection settings, such as Client ID, Client Secret, authentication settings, and any provider-specific fields.
  4. Save the connection.

Initialise the parameters property

Some provider-specific settings are added to the Parameters property only after they have been assigned or modified. If a field such as Scope is left empty, it may not appear in Parameters yet.

  1. Open the field you need (e.g. Scope, SpreadSheetId, SiteId, DriveId).
  2. Enter a value or modify the existing one.
  3. Save the connection.
  4. The field will now appear as an entry inside the Parameters property, ready to be targeted with an expression.
    image
    image1489×728 71.8 KB

Assign an expression to a standard property

Common properties such as ClientId, ClientSecret, UserName, and Password can be assigned directly using an SSIS Expression. This is the recommended approach whenever the property exists individually in the Properties window.

  1. Select the API Connection Manager.

  2. Open the Expressions editor.

  3. Assign the property, for example ClientSecret, to an SSIS variable: @[User::ClientSecret]

  4. Save the configuration. For more information, check this article

    image
    image431×336 4.96 KB

Assign an expression to the Parameters property

Provider-specific settings such as SpreadSheetId or TabName are stored inside the Parameters property as a JSON array.

  1. Open the Expressions editor for the connection.
  2. Select the Parameters property.
  3. Replace the fixed value with an SSIS expression that concatenates the JSON literal with your variable(s), for example:
"[{Name:\"Scope\",Value:\"https://www.googleapis.com/auth/spreadsheets~https://www.googleapis.com/auth/spreadsheets.readonly~https://www.googleapis.com/auth/drive~https://www.googleapis.com/auth/drive.readonly\"},{Name:\"SpreadSheetId\",Value:\"" + @[User::spreadSheetIdVar] + "\"},{Name:\"TabName\",Value:\"" + @[User::tableNameVar] + "\"}]"

  1. Evaluate the expression and save the configuration.

    image
    image1210×593 35.4 KB

  2. At runtime, SSIS replaces the variable value before the API request is executed.

Conclusion

Common properties such as ClientId and ClientSecret can be configured directly using SSIS Expressions, while provider-specific settings can be customised through the Parameters property.

By combining SSIS variables with expressions, you can easily switch credentials, resources, and provider-specific settings at runtime without modifying the Connection Manager. This approach simplifies package deployment across multiple environments and makes your SSIS solutions easier to maintain.

Still need help?

If you need assistance configuring expressions for your API Connection Manager, please contact our support team: