SSIS: How to Handle OAuth Refresh Token and Automatically Get New Access Token (Using ZappySys)

SSIS PowerPack
, ,
1

When working with APIs that use OAuth 2.0 authentication, one common challenge is handling expired access tokens and refreshing them automatically using a refresh token.

In this article, we will walk through how to correctly configure token refresh logic in SSIS using ZappySys components.

Introduction

Most APIs issue:

  • Access Token (short-lived)
  • Refresh Token (long-lived)

When the access token expires, you must call the token endpoint again using the refresh token to obtain a new access token.

If this step is not configured correctly, you may encounter:

  • 401 Unauthorized errors
  • Token refresh failures
  • Scheduled jobs breaking after some time

Prerequisites

Before you begin, ensure:

  • OAuth authentication is already working
  • You have client_id, client_secret, and refresh_token
  • Token endpoint URL is available

Step 1: Configure Token Refresh API Call

Use ZappySys API Source / REST API Task / OAuth Connection Manager to call the token endpoint.

Example

POST https://api.example.com/oauth/token

Step 2: Setup Request Body

Configure the request body as:

grant_type=refresh_token
&refresh_token={EncodedRefreshToken}
&client_id={CLIENT_ID}

Step 3: Encode Refresh Token using Placeholder Function

Refresh tokens often contain special characters which must be URL encoded.

Use ZappySys Placeholder function:

{FUN_URLENC(@[User::RefreshToken])}

This ensures:

  • Proper encoding of special characters
  • Prevents invalid request errors
  • Improves compatibility with strict APIs

Step 4: Configure Authentication

Some APIs require client credentials in the Authorization header instead of request body.

Use Basic Authentication

Authorization: Basic BASE64(client_id:client_secret)

This is a critical step.

Incorrect configuration here may result in:

  • Unauthorized (401) response
  • Token refresh failure

Step 5: Add Required Headers

Content-Type: application/x-www-form-urlencoded
Authorization: Basic <encoded_value>

Step 6: Parse Response and Store Tokens

A successful response typically contains:

{
“access_token”: “new_access_token”,
“refresh_token”: “new_refresh_token”,
“expires_in”: 1800
}

In ZappySys:

  • Go to Response tab
  • Extract values using JSON Path:

$.access_token
$.refresh_token

  • Store values in SSIS variables

Step 7: Handle Changing Refresh Token

Some APIs rotate refresh tokens on every request.

This means:

  • A new refresh token is returned each time
  • Old refresh token becomes invalid

You must always:

  • Update stored refresh token
  • Use latest value for next request

For detailed implementation, refer to:

https://zappysys.zendesk.com/hc/en-us/articles/115004555334-How-to-handle-Changing-OAuth-RefreshToken-in-SSIS-ODBC

Step 8: Testing Token Refresh

To test without waiting for expiration:

  • Use token revoke endpoint (if available)

Example:

POST /oauth/revoke
token={ACCESS_TOKEN}

This forces token expiration and helps validate refresh logic.

Common Issues and Fixes

Issue: Unauthorized error during refresh

  • Check if client_secret should be in header instead of body

Issue: Token refresh works once but fails later

  • Ensure refresh token is updated dynamically

Issue: Invalid request error

  • Use FUN_URLENC for encoding refresh token

Issue: Works in Postman but fails in SSIS

  • Match headers and body exactly

Conclusion

By correctly configuring:

  • Request body
  • Authentication method
  • Token parsing and storage

You can fully automate OAuth token refresh in SSIS using ZappySys.

This ensures your packages run reliably without manual token updates.

Need Help?

If you still face issues:

  • Validate API behavior in Postman
  • Double-check header vs body requirements
  • Share logs/screenshots with ZappySys support team:
    * Live Chat: Open the chat widget (bottom right of this page)
    * Email: support@zappysys.com
    * Support Center: Support | ZappySys