Getting next token using Rest Connector

tmumaw — Thu, 23 Dec 2021 14:42:54 GMT

Hi All,

Need a little help. I'm trying to extract data form Microsoft CRM365 using Rest Connector. Microsoft limits me to 5000 row per pull with a max time limit of 60 minutes. I need more than 60 minutes processing time so I'm trying to refresh the token however I'm not having any luck. Does anyone see anything wrong with my script why it would not pull another token? Sort of under the gun to get this to work. Thanks.

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://login.microsoftonline.com/czzzz%3D;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=anonymous;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryHeaders=Content-Type%2application/x-www-form-urlencoded;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=TMXZBGC;XPassword=FRRfAdD;";
RestConnectorMasterTable:
SQL SELECT
"token_type",
"expires_in",
"ext_expires_in",
"expires_on",
"not_before",
"resource",
"access_token"
FROM JSON (wrap on) "root";

[root]:
LOAD
[token_type],
[expires_in],
[ext_expires_in],
[expires_on],
[not_before],
[resource],
[access_token]
RESIDENT RestConnectorMasterTable;

LET vToken = peek('access_token');

LET vExpiresIn = peek('expires_in',0,'access_token');
LET vExpiresIn = peek('expires_on',0,'access_token');
LET vAccessToken = peek('access_token',0,'access_token');
LET vTokenExpires = num(timestamp(now()) + $(vExpiresIn)/86400);

DROP TABLE RestConnectorMasterTable;

LET vURL = 'https://davey.api.crm.dynamics.com/api/data/v9.1/zopportunities';

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=3600;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=anonymous;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=$filter%2davey_contractstartdate ge 2019-01-01 and davey_proposaltype eq 2;queryHeaders=OData-Version%24.0%1OData-MaxVersion%24.0%1Authorization%2Bearer $(vToken);PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=FDQJXMC;XPassword=EWcUPYA;";

DO while LEN(vURL) > 0
RestConnectorMasterTable:
SQL SELECT
"@odata.context",
"@odata.nextLink",
"__KEY_root",
(SELECT
"@odata.etag",
"createdon",
"opportunityid",
"__FK_value"
FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (Url "$(vURL)");

[Opportunity]:
LOAD
[@odata.etag],
[createdon],
[opportunityid],
[__FK_value] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);

STORE * FROM [RestConnectorMasterTable] into QVD\Opportunity365.QVD;

LET vCount = FieldValueCount('@odata.nextLink');
LET vURL_new = FieldValue('@odata.nextLink', vCount);

IF vTokenExpires <= Num(now()) THEN
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://login.microsoftonline.com/c4a8639a-d5a9-4b9a-aa77-c15cb9c9b6b2/oauth2/token;timeout=1800;method=POST;requestBody=client_id%244d54725-49d3-4966-93cb-c26fcd804ade&grant_type%2client_credentials&resource%2https%%3A%%2F%%2Fdavey.api.crm.dynamics.com%%2F&client_secret%2b7zqDA0tJh0nuk7imXGYnkGRqrV9ycRE1L6H5elld%%2Bk%%3D;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=anonymous;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryHeaders=Content-Type%2application/x-www-form-urlencoded;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=TMXZBGC;XPassword=FzzRRfAdD;";
RestConnectorMasterTable:
SQL SELECT
"token_type",
"expires_in",
"ext_expires_in",
"expires_on",
"not_before",
"resource",
"access_token"
FROM JSON (wrap on) "root";

[root]:
LOAD
[token_type],
[expires_in],
[ext_expires_in],
[expires_on],
[not_before],
[resource],
[access_token]
RESIDENT RestConnectorMasterTable;

LET vToken = peek('access_token');
LET vExpiresIn = peek('expires_in',0,'access_token');
LET vAccessToken = peek('access_token',0,'access_token');
LET vTokenExpires = Num(timestamp(now() + $(vExpiresIn)/86400));

LET vToken = peek('access_token');

DROP TABLE RestConnectorMasterTable;
end if

IF(vURL <> vURL_new) then
vURL = vURL_new;
ELSEIF(vURL = vURL_new) then
vURL = '';
end if

Trace $(vURL);

Loop

DROP TABLE RestConnectorMasterTable;

DROP TABLE OpportunityProducts;

DROP TABLE product_opportunities;

Exit SCRIPT;

topic Getting next token using Rest Connector in Connectivity & Data Prep

Getting next token using Rest Connector