How to handle Changing OAuth RefreshToken in SSIS / ODBC ?

Some APIs that use OAuth 2.0 as authentication work strangely and when acquiring an AccessToken they keep changing RefreshToken with each request. API like VSTS (TFS Online) API, Bing Ads API. 

There are two approaches for this type of issue. You can set Refresh Token File Path to handle changing refresh token (see method-1). If you are using older version of ZappySys SSIS PowerPack then you may have to use Method-2 but for most customers Method-1 would be perfect solution.

Method-1

On the OAuth Connection setup path to save changing RefreshToken value.

NOTE: Just specify an empty file, don't write anything into it.

Method-2

Then you have you to use OAuth Connection Manager once, retrieve your first AccessToken and RefreshToken and then handle issuing AcccessToken yourself.

Configure your OAuth Connection Manager, press Generate Token. When a dialog appears asking whether you want to save AccessToken and RefreshToken to a file, click YES:

Create two variables AccessToken and RefreshToken and assign the values you have in the file you saved. You can safely delete OAuth Connection Manager and forget it:

Then just use REST API Task to issue yourself a new AccessToken:

Web URL must be a URL you use to request for an AccessToken (just like in the OAuth Connection Manager), usually followed by access_token URL parameter. Use your User::AccessToken variable for it. If you are unsure what this URL should be, use Fiddler in a step above, when you press Generate Token. You should see what exact URL is used.

Request body should be set accordingly. Fill in client_id, client_secret and then use User::RefreshToken variable for refresh_token:

BODY TEXT REPRESENTATION:

client_id=XXXXXXXXXXXXX&client_secret=XXXXXXXXXXXXX&grant_type=refresh_token&refresh_token={{User::RefreshToken}}

Now configure REST API Task to save a response into a file, this will persist AccessToken and RefreshToken:

Press Test Request/Response.

Response.json should look similar to this:

     {
       "access_token":"mF_9.B5f-4.1JqM",
       "token_type":"Bearer",
       "expires_in":3600,
       "refresh_token":"tGzv3JOkF0XG5Qx2TlKWIA"
     }

Now you are ready to parse this JSON response into access_token and refresh_token. Add JSON Parser Task and connect it with REST API Task and use the same Response.json file where you saved the response using REST API Task:

You are almost ready to make an API call. But to make SSIS package work each time you launch it, you will need to pass existing (lastly generated) AccessToken and RefreshToken to your REST API Task, so you need to add another JSON Parser Task at the front (just make a copy of existing one) of your OAuth handling. So your SSIS should look similar to this:

Finally, you are able to make a call using the last REST API Task. What you have to do at this point is just add an HTTP header named Authorization with value "Bearer {{User::AccessToken}}":

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/