I have never used the REST connector before. So this is a first time for me. Here is what I have so far. Any help would be appreciated.
Connect String
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');
DROP TABLE RestConnectorMasterTable;
LET accountsURL = 'https://daveyqas.api.crm.dynamics.com/api/data/v9.0/accounts';
CUSTOM CONNECT TO
RestConnectorMasterTable:
SQL SELECT
"@odata.context",
"__KEY_root",
(SELECT
"@odata.etag",
"paymenttermscode",
"davey_saptext",
"mcala_lastemaildate",
"address1_line1",
"mcala_lastactivitytype",
"merged",
"davey_accountgroup",
"_modifiedby_value",
"territorycode",
"emailaddress1",
"davey_donotvalidateaddress",
"exchangerate",
"davey_createnewprimarycontact",
"davey_cpbusinesspartner",
"mcala_lastactivitydate",
"name",
"accountcategorycode",
"_owningbusinessunit_value",
"_primarycontactid_value",
"davey_source",
"davey_stateprovince",
"address1_composite",
"davey_accountbalance_base",
"donotpostalmail",
"accountratingcode",
"marketingonly",
"davey_noupdatetobillingscheduleflag",
"donotphone",
"preferredcontactmethodcode",
"_ownerid_value",
"address1_telephone1",
"description",
"davey_clientrelationshipfolder",
"customersizecode",
"davey_lastpaymentamount",
"openrevenue_date",
"address2_addresstypecode",
"businesstypecode",
"donotemail",
"address2_shippingmethodcode",
"timezoneruleversionnumber",
"address1_addressid",
"davey_renewalallowed",
"address2_freighttermscode",
"statuscode",
"createdon",
"versionnumber",
"address1_stateorprovince",
"davey_renewalmonth",
"davey_baddebt",
"davey_country",
"donotsendmm",
"donotfax",
"davey_clientsince",
"donotbulkpostalmail",
"davey_addressaccepted",
"address1_country",
"davey_taxexempted",
"followemail",
"davey_evergreenstatus",
"address1_line2",
"modifiedon",
"creditonhold",
"_davey_salesarborist_value",
"davey_bill_sched_block",
"_transactioncurrencyid_value",
"accountid",
"davey_addressunvalidated",
"donotbulkemail",
"accountclassificationcode",
"davey_accountname2",
"davey_daveyemployeeflag",
"davey_clientclass",
"mca_sapaccountid",
"shippingmethodcode",
"mcala_nextapptdate",
"mcala_nextactivitytype",
"_createdby_value",
"address1_city",
"davey_sortzip",
"mcala_nextactivitydate",
"davey_industrycode",
"davey_renewaldeliverypreference",
"davey_blockedforsales",
"telephone1",
"davey_ranrenewalyear",
"_owningteam_value",
"participatesinworkflow",
"davey_lastpaymentamount_base",
"statecode",
"overriddencreatedon",
"address2_addressid",
"address1_postalcode",
"davey_accountbalance",
"davey_lastpaymentdate",
"opendeals_date",
"davey_sortterm",
"aging60",
"mca_datesenttosap",
"address2_upszone",
"sharesoutstanding",
"address2_stateorprovince",
"address1_longitude",
"importsequencenumber",
"address2_longitude",
"sic",
"address2_telephone2",
"entityimage",
"address2_composite",
"entityimage_timestamp",
"traversedpath",
"primarytwitterid",
"marketcap_base",
"davey_servicecontactpreference",
"davey_propertycreatememo",
"openrevenue",
"address1_line3",
"fax",
"stageid",
"_preferredsystemuserid_value",
"customertypecode",
"numberofemployees",
"emailaddress2",
"processid",
"address2_primarycontactname",
"address2_utcoffset",
"davey_callfirst",
"address2_city",
"stockexchange",
"creditlimit_base",
"address1_latitude",
"timespentbymeonemailandmeetings",
"address2_name",
"utcconversiontimezonecode",
"davey_marketingchannel",
"_originatingleadid_value",
"address2_postofficebox",
"telephone2",
"address2_latitude",
"revenue",
"entityimageid",
"address2_fax",
"address2_telephone1",
"address1_shippingmethodcode",
"lastusedincampaign",
"address1_telephone3",
"entityimage_url",
"marketcap",
"address2_county",
"address1_freighttermscode",
"openrevenue_base",
"address1_primarycontactname",
"davey_arboristsrenewalnotes",
"mcala_lasttaskdate",
"tickersymbol",
"_davey_propertyid_value",
"_parentaccountid_value",
"creditlimit",
"industrycode",
"accountnumber",
"davey_crmaudit",
"address2_telephone3",
"aging90",
"aging90_base",
"_preferredequipmentid_value",
"_defaultpricelevelid_value",
"_slainvokedid_value",
"address1_telephone2",
"address2_line1",
"address1_postofficebox",
"openrevenue_state",
"address1_utcoffset",
"ownershipcode",
"davey_primarycontactfullname",
"_territoryid_value",
"davey_specialcustomer",
"_slaid_value",
"revenue_base",
"_owninguser_value",
"davey_acquisitioncode",
"_modifiedbyexternalparty_value",
"opendeals_state",
"davey_jobsitetext",
"onholdtime",
"aging30_base",
"mcala_nextphonecalldate",
"_preferredserviceid_value",
"aging60_base",
"davey_referredbynote",
"davey_referredon",
"address1_name",
"_modifiedonbehalfby_value",
"yominame",
"address1_addresstypecode",
"address1_county",
"_createdonbehalfby_value",
"address2_line2",
"opendeals",
"ftpsiteurl",
"address2_country",
"aging30",
"_davey_previousaccount_value",
"davey_laststatusupdatedate",
"address1_fax",
"davey_marketingsubchannel",
"davey_accounttype",
"primarysatoriid",
"mcala_nexttaskdate",
"preferredappointmenttimecode",
"preferredappointmentdaycode",
"_createdbyexternalparty_value",
"_masterid_value",
"emailaddress3",
"address1_upszone",
"_davey_referredbyid_value",
"mcala_lastphonecalldate",
"davey_acquiredfrom",
"mcala_lastapptdate",
"address2_postalcode",
"websiteurl",
"lastonholdtime",
"telephone3",
"address2_line3",
"__FK_value"
FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root";
[Account]:
LOAD [@odata.etag],
[paymenttermscode],
[davey_saptext],
[mcala_lastemaildate],
[address1_line1],
[mcala_lastactivitytype],
[merged],
[davey_accountgroup],
[_modifiedby_value],
[territorycode],
[emailaddress1],
[davey_donotvalidateaddress],
[exchangerate],
[davey_createnewprimarycontact],
[davey_cpbusinesspartner],
[mcala_lastactivitydate],
[name],
[accountcategorycode],
[_owningbusinessunit_value],
[_primarycontactid_value],
[davey_source],
[davey_stateprovince],
[address1_composite],
[davey_accountbalance_base],
[donotpostalmail],
[accountratingcode],
[marketingonly],
[davey_noupdatetobillingscheduleflag],
[donotphone],
[preferredcontactmethodcode],
[_ownerid_value],
[address1_telephone1],
[description],
[davey_clientrelationshipfolder],
[customersizecode],
[davey_lastpaymentamount],
[openrevenue_date],
[address2_addresstypecode],
[businesstypecode],
[donotemail],
[address2_shippingmethodcode],
[timezoneruleversionnumber],
[address1_addressid],
[davey_renewalallowed],
[address2_freighttermscode],
[statuscode],
[createdon],
[versionnumber],
[address1_stateorprovince],
[davey_renewalmonth],
[davey_baddebt],
[davey_country],
[donotsendmm],
[donotfax],
[davey_clientsince],
[donotbulkpostalmail],
[davey_addressaccepted],
[address1_country],
[davey_taxexempted],
[followemail],
[davey_evergreenstatus],
[address1_line2],
[modifiedon],
[creditonhold],
[_davey_salesarborist_value],
[davey_bill_sched_block],
[_transactioncurrencyid_value],
[accountid],
[davey_addressunvalidated],
[donotbulkemail],
[accountclassificationcode],
[davey_accountname2],
[davey_daveyemployeeflag],
[davey_clientclass],
[mca_sapaccountid],
[shippingmethodcode],
[mcala_nextapptdate],
[mcala_nextactivitytype],
[_createdby_value],
[address1_city],
[davey_sortzip],
[mcala_nextactivitydate],
[davey_industrycode],
[davey_renewaldeliverypreference],
[davey_blockedforsales],
[telephone1],
[davey_ranrenewalyear],
[_owningteam_value],
[participatesinworkflow],
[davey_lastpaymentamount_base],
[statecode],
[overriddencreatedon],
[address2_addressid],
[address1_postalcode],
[davey_accountbalance],
[davey_lastpaymentdate],
[opendeals_date],
[davey_sortterm],
[aging60],
[mca_datesenttosap],
[address2_upszone],
[sharesoutstanding],
[address2_stateorprovince],
[address1_longitude],
[importsequencenumber],
[address2_longitude],
[sic],
[address2_telephone2],
[entityimage],
[address2_composite],
[entityimage_timestamp],
[traversedpath],
[primarytwitterid],
[marketcap_base],
[davey_servicecontactpreference],
[davey_propertycreatememo],
[openrevenue],
[address1_line3],
[fax],
[stageid],
[_preferredsystemuserid_value],
[customertypecode],
[numberofemployees],
[emailaddress2],
[processid],
[address2_primarycontactname],
[address2_utcoffset],
[davey_callfirst],
[address2_city],
[stockexchange],
[creditlimit_base],
[address1_latitude],
[timespentbymeonemailandmeetings],
[address2_name],
[utcconversiontimezonecode],
[davey_marketingchannel],
[_originatingleadid_value],
[address2_postofficebox],
[telephone2],
[address2_latitude],
[revenue],
[entityimageid],
[address2_fax],
[address2_telephone1],
[address1_shippingmethodcode],
[lastusedincampaign],
[address1_telephone3],
[entityimage_url],
[marketcap],
[address2_county],
[address1_freighttermscode],
[openrevenue_base],
[address1_primarycontactname],
[davey_arboristsrenewalnotes],
[mcala_lasttaskdate],
[tickersymbol],
[_davey_propertyid_value],
[_parentaccountid_value],
[creditlimit],
[industrycode],
[accountnumber],
[davey_crmaudit],
[address2_telephone3],
[aging90],
[aging90_base],
[_preferredequipmentid_value],
[_defaultpricelevelid_value],
[_slainvokedid_value],
[address1_telephone2],
[address2_line1],
[address1_postofficebox],
[openrevenue_state],
[address1_utcoffset],
[ownershipcode],
[davey_primarycontactfullname],
[_territoryid_value],
[davey_specialcustomer],
[_slaid_value],
[revenue_base],
[_owninguser_value],
[davey_acquisitioncode],
[_modifiedbyexternalparty_value],
[opendeals_state],
[davey_jobsitetext],
[onholdtime],
[aging30_base],
[mcala_nextphonecalldate],
[_preferredserviceid_value],
[aging60_base],
[davey_referredbynote],
[davey_referredon],
[address1_name],
[_modifiedonbehalfby_value],
[yominame],
[address1_addresstypecode],
[address1_county],
[_createdonbehalfby_value],
[address2_line2],
[opendeals],
[ftpsiteurl],
[address2_country],
[aging30],
[_davey_previousaccount_value],
[davey_laststatusupdatedate],
[address1_fax],
[davey_marketingsubchannel],
[davey_accounttype],
[primarysatoriid],
[mcala_nexttaskdate],
[preferredappointmenttimecode],
[preferredappointmentdaycode],
[_createdbyexternalparty_value],
[_masterid_value],
[emailaddress3],
[address1_upszone],
[_davey_referredbyid_value],
[mcala_lastphonecalldate],
[davey_acquiredfrom],
[mcala_lastapptdate],
[address2_postalcode],
[websiteurl],
[lastonholdtime],
[telephone3],
[address2_line3],
[__FK_value] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);
STORE * FROM [RestConnectorMasterTable] into qvd\Accounts365.QVD;
;
//[root]:
//LOAD [@odata.context],
// [__KEY_root]
//RESIDENT RestConnectorMasterTable
// WHERE NOT IsNull([__KEY_root]);
DROP TABLEs RestConnectorMasterTable, Account;
Exit SCRIPT;
