In the introduction of this blog post series, I explained the use case: extracting data from the Planview Portfolios RESET API using Azure Data Factory. Any tool that can send HTTPS requests can work with the REST API, but our focus here is ADF (which can also be Synapse Pipelines or Fabric Pipelines).
In the second part, I explained how to access the OData service of Planview Portfolios, which returns a list of the projects and their IDs. In this part, we’ll focus on fetching the OAuth access token needed for authentication. The swagger page gives us the following information about the OAuth endpoint:
There are three parameters:
- The grant type, which is always “client_credentials”.
- The client ID. The client is created by a Planview administrator in the OAuth Clients menu. Click main menu > Administration > Users and Roles > OAuth clients. The OAuth client inherits the permissions of the user who created the client.
- The client secret, which is like a password so store it securely.
In the Web Activity, we need to enter the following URL:
https://myaccount.pvcloud.com/myserver/public-api/v1/oauth/token
Configure POST as the method and set authentication to None. And now comes the tricky part. From the swagger example, you might think we need to pass the following JSON as the body:
{
"grant_type": "client_credentials",
"client_id": "myClientID",
"client_secret": "myClientSecret"
}
However, this is not the case. We get an error saying the required fields cannot be found:
Maybe the JSON could not be parsed? Supplying a header with “Content-Type” = “application/json” doesn’t help. Even putting the @JSON function around the body in an expression – (to make sure it’s really JSON and not just a string that is sent – doesn’t work. However, if we take a look at the swagger page and at the example cURL command that is sent, we see it’s not a JSON body that is used:
Instead, multi-part form data is used, as can be derived from the content type header. Typically this content type is used to upload binary files, but with Planview they use it to let you fetch an access token. If this sounds complicated, it is. To get the REST API call to work, we need to specify the following content type:
multipart/form-data;boundary="MyBoundary"
Alongside multipart/form-data, we also need to specify a boundary. This is so we can send multiple parts in one message, and the parts are separated by the boundary. With the information found in this blog post, I was able to construct the following body:
--MyBoundary Content-Disposition: form-data; name="grant_type" client_credentials --MyBoundary Content-Disposition: form-data; name="client_id" myclientid --MyBoundary Content-Disposition: form-data; name="client_secret" myclientsecret --MyBoundary--
Yes, I’m aware that it looks weird. Each part of the multip-part form data starts with the boundary that we defined in the content type, which is “–MyBoundary” (it is custom apparently to start with a couple of dashes). Then we put “Content-Disposition: form-data;”, followed by the name of the field. Then we add an empty line (very important, but don’t ask me why) and we put the value of the form field. For the grant type for example, this is “client_credentials”.
We repeat this for the client ID and the client secret, and we repeat the custom boundary one more time, followed by two dashes (who invented this?). The configuration of the Web activity now looks like this:
I also added an “accept” header with the value “application/json”, but this is probably redundant since ADF only accepts JSON as responses from web services. However, this still fails with the error “Failed to read the request form. Line length limit 100 exceeded”.
My mood at the time:
When we take a look at the actual message that is being sent, we can see ADF uses only a linefeed character – also known as n – but not a carriage return (known as r).
Windows systems typically use rn, while Unix systems only use n (does this mean ADF runs on Linux?). So we need to make sure rn is sent instead of n. To do this, we need to use an expression where we concatenate everything together with the correct line breaks (and make sure you don’t hit enter yourself, it has to be one long line of text!). Using rn doesn’t work (ADF doesn’t recognize this), but with the help of the blog post ADF: Replacing carriage returns in Data Factory expression language by Greg Low, I was able to construct the following expression:
@concat('--MyBoundary',base64ToString('DQo='),'Content-Disposition: form-data; name="grant_type"',base64ToString('DQo='),base64ToString('DQo='),'client_credentials',base64ToString('DQo='),'--MyBoundary',base64ToString('DQo='),'Content-Disposition: form-data; name="client_id"',base64ToString('DQo='),base64ToString('DQo='),'myclientid',base64ToString('DQo='),'--MyBoundary',base64ToString('DQo='),'Content-Disposition: form-data; name="client_secret"',base64ToString('DQo='),base64ToString('DQo='),'myclientsecret',base64ToString('DQo='),'--MyBoundary--')The trick is to use base64ToString(‘DQo=’) as the carriage return / line feed character. With this expression, I was finally able to fetch an access token (which is valid for 3600 seconds).
In the last part of this series, we’ll use this access token and the list of project IDs to fetch the work for each project.
The other parts of this series:
- Extracting Data from the Planview Portfolios REST API using ADF – Introduction
- Planview Portfolios REST API with ADF – The OData Feed
- Planview Portfolios REST API with ADF – Retrieving Actual Data
The post Planview Portfolios REST API with ADF – Fetching the Access Token first appeared on Under the kover of business intelligence.