0

I am trying to get all the records(800000) updated in to a data extension but it is allowing me to get 10000 records at a time. I am using POST API call, SSJS in my cloud pages to update in a data extension. Is there any other approach which could be automated to get the complete data instead of doing separate calls for all the records?

Thanks in advance!

1

You can accomplish this with wsProxy and pagination:

var prox = new Script.Util.WSProxy(),
    objectType = "Email",
    cols = ["Name"],
    moreData = true,
    reqID = null,
    numItems = 0;

while(moreData) {
    moreData = false;
    var data = reqID == null ?
           prox.retrieve(objectType, cols) :
           prox.getNextBatch(objectType, reqID);

    if(data != null) {
        moreData = data.HasMoreRows;
        reqID = data.RequestID;
        if(data && data.Results) {
            for(var i=0; i< data.Results.length; i++) {
                Platform.Response.Write(data.Results[i].Name);
                numItems++;
            }
        }
    }
}
Platform.Response.Write("<br />" + numItems + " total " + objectType);

Depending on how long it takes to run, you might want to consider running it in a script activity instead of a cloudpage. I've found that cloudpages time out a lot faster than a script activity.

Docs: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/ssjs_WSProxy_advanced_retrieve.htm

| improve this answer | |
0

A record based operation through the API is probably not the right approach to the problem as Marketing Cloud's API is not meant for bulk operations of this kind. If facing 5, 6, 7 digit numbers of records, Marketing Cloud will nudge you towards using SQL more or less harshly through API limits like the one you witness.

You can execute automations or individual sql queries through the API. You can also assemble those queries through the API. This gives you the adhoc execution potential together with the necessary performance. You can write those API calls in SSJS.

Of course this often requires some infrastructure in the form of data extensions, as each SQL query needs a target DE.

Example API calls - you can literally run these three in sequence. Create query, retrieve its objectId, perform the query execution. There is no automation required around the query activity, you execute the raw activity. This actually has performed significantly faster for me than waiting for the automation queue.

All calls go to this URL:

POST {{tenantSpecificSoapEndpoint}}/Service.asmx

Create an update query through SOAP API:

Content-Type: text/xml;charset=UTF-8
SOAPAction: "Create" 
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Header>
        <fueloauth>TOKEN</fueloauth>
    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
        <CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <Options></Options>
            <Objects xsi:type="QueryDefinition">
                <PartnerKey xsi:nil="true"></PartnerKey>
                <ObjectID xsi:nil="true"></ObjectID>
                <!--<CategoryID>68371</CategoryID>-->
                <Name>Name of Query</Name>
                <Description>test</Description>
                <QueryText>SELECT Field FROM DE WHERE Filter = 'test'</QueryText>
                <TargetType>DE</TargetType>
                <DataExtensionTarget>
                    <PartnerKey xsi:nil="true"></PartnerKey>
                    <ObjectID xsi:nil="true"></ObjectID>
                    <CustomerKey>Customer Key of Target DE</CustomerKey>
                    <Name>Name of Target DE</Name>
                </DataExtensionTarget>
                <TargetUpdateType>Update</TargetUpdateType>
            </Objects>
        </CreateRequest>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>


Retrieve a query to get its objectId. Note the retrieve filter is checking for "active" status as well - important, as you could also retrieve deleted query activities, where name or CustomerKey are no longer enforced to be unique!

Content-Type: text/xml
SOAPAction: "Retrieve"
<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <fueloauth xmlns="http://exacttarget.com">{{accessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>QueryDefinition</ObjectType>
                <Properties>Name</Properties>
                <Properties>Description</Properties>
                <Properties>CustomerKey</Properties>
                <Properties>ObjectID</Properties>
                <Properties>Status</Properties>
                <Filter xsi:type="par:ComplexFilterPart" xmlns:par="http://exacttarget.com/wsdl/partnerAPI">
                <LeftOperand xsi:type="par:SimpleFilterPart">
                  <Property>Status</Property>
                  <SimpleOperator>equals</SimpleOperator>
                  <Value>Active</Value>
               </LeftOperand>
               <LogicalOperator>AND</LogicalOperator>
               <RightOperand xsi:type="par:SimpleFilterPart">
                  <Property>Name</Property>
                  <SimpleOperator>equals</SimpleOperator>
                  <Value>yourQueryActivity</Value>
               </RightOperand>
            </Filter>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>

Perform a query by objectId:

Content-Type: text/xml
SOAPAction: "Perform"
<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <fueloauth xmlns="http://exacttarget.com">{{accessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <PerformRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI" xmlns:ns2="urn:fault.partner.exacttarget.com">
            <Action>start</Action>
            <Definitions>
                <Definition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="QueryDefinition">
                    <ObjectID>{{activityObjectId}}</ObjectID>
                </Definition>
            </Definitions>
        </PerformRequestMsg>
    </s:Body>
</s:Envelope>

here is the first payload in a very simple (very literal) SSJS frame. Of course you can get more elegant with WSProxy, but this is easy to start from:

<script type="javascript" runat="server">
Platform.Load("Core","1");

/* define variables here */

try {
  var url = soapEndpoint + '/Service.asmx';
  var contentType = 'text/xml; charset=UTF-8';
  var headerNames = ["SOAPAction"];
  var headerValues = ["Create"];
  var payload = "";

  payload += "<SOAP-ENV:Envelope xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance'>"
  payload += "    <SOAP-ENV:Header>"
  payload += "        <fueloauth>TOKEN</fueloauth>"
  payload += "    </SOAP-ENV:Header>"
  payload += "    <SOAP-ENV:Body>"
  payload += "        <CreateRequest xmlns='http://exacttarget.com/wsdl/partnerAPI'>"
  payload += "            <Options></Options>"
  payload += "            <Objects xsi:type='QueryDefinition'>"
  payload += "                <PartnerKey xsi:nil='true'></PartnerKey>"
  payload += "                <ObjectID xsi:nil='true'></ObjectID>"
  payload += "                <!--<CategoryID>68371</CategoryID>-->"
  payload += "                <Name>Name of Query</Name>"
  payload += "                <Description>test</Description>"
  payload += "                <QueryText><[CDATA[SELECT Field FROM DE WHERE Filter = 'test']]≥</QueryText>"
  payload += "                <TargetType>DE</TargetType>"
  payload += "                <DataExtensionTarget>"
  payload += "                    <PartnerKey xsi:nil='true'></PartnerKey>"
  payload += "                    <ObjectID xsi:nil='true'></ObjectID>"
  payload += "                    <CustomerKey>Customer Key of Target DE</CustomerKey>"
  payload += "                    <Name>Name of Target DE</Name>"
  payload += "                </DataExtensionTarget>"
  payload += "                <TargetUpdateType>Update</TargetUpdateType>"
  payload += "            </Objects>"
  payload += "        </CreateRequest>"
  payload += "    </SOAP-ENV:Body>"
  payload += "</SOAP-ENV:Envelope>"

  var result = HTTP.Post(url,contentType,payload,headerNames,headerValues)
  var statusCode = result["StatusCode"]
  var tsResponse = result["Response"][0]

}
catch (err)  
{

      Write("error" + err)

}
</script>



| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.