Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am having a problem looping through a table using the rest connector. I can get my first 5000 rows of data, but my nexturl variable is not getting populated. Does anyone see anything that would make it return a null value? Thanks
LET i = 1;
LET vURL = 'https://xyz.api.crm.dynamics.com/api/data/v9.0/accounts';
DO while LEN(vURL) > 0
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=30;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=anonymous;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStorbeeName=My;queryHeaders=OData-MaxVersion%24.0%1OData-Version%24.0%1Authorization%2Bearer $(vToken);PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=ZCASARB;XPassword=GbTdcBA;";
RestConnectorMasterTable:
SQL SELECT
"@odata.context",
"@odata.nextLink",
"__KEY_root",
(SELECT
"@odata.etag",
"accountid",
"__FK_value"
FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root";
[Accounts]:
LOAD
[@odata.etag],
[accountid],
[__FK_value] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);
//LET vURL = FieldValue(@odata.nextLink,1);
LET vURL = @odata.nextLink;
//DROP TABLE RestConnectorMasterTable;
LET i=i+1;
Loop
DROP TABLE RestConnectorMasterTable;
Try LET vURL = Peek('@odata.nextLink',0,'RestConnectorMasterTable');
And modify your selections statement to include a WITH CONNECTION statement to dynamically change the url:
RestConnectorMasterTable:
SQL SELECT
"@odata.context",
"@odata.nextLink",
"__KEY_root",
(SELECT
"@odata.etag",
"accountid",
"__FK_value"
FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (URL '$(vURL)');
But it should be possible to use the standard NEXT URL pagination option of the rest connector:
root\@odata.nextLink should be the path I think you need to specify.
Nope. Still only brought back first 5000 rows. Thanks
Your connect string says "PaginationType=None", so I guess it simply loads first page of data only. See help page for details how to load paged data.
Juraj