How to iterate on more than 2500 records in a DE with SSJS?

Question

I'm facing an issue about a server side Javascript code that I put in place to deal with data management of a column. I have one data extension which contains 3 columns : ID_global, Tech_contact_id, OLD_ID. This DE is filled by almost 200 000 records.

In the column old_id, I have multiple IDs that are seperated by ";". The number of ID is random so it can be 1 or N ids I'm trying to fill another DE with the same 3 columns (ID_global, Tech_contact_id, OLD_ID) but with managing the split of OLD_ID by the ";" separator in order to get 1 ID for each record.

Here is an exemple :

Become :

To do so I coded this script which is doing the job expected with a small amount of records (1000):

 <script runat="server">
  Platform.Load('core', '1.1.1');

  var initDE = DataExtension.Init("Customer key of DE 1");
  var initDEData = initDE.Rows.Retrieve();
  var targetDE = DataExtension.Init("Customer key of DE 2");
  
  for (var i = 0; i < initDEData.length; i++) {
      var id_glob = initDEData[i].Global_ID;
      var id_contact = initDEData[i].Tech_contact_id;
      var id_old = initDEData[i].Old_id;
      var array_id = [];
      array_id = id_old.split(';');
      for (var j = 0; j < array_id.length; j++){
        var processed = targetDE.Rows.Add([{Global_ID:id_glob, Old_id:array_id[j], Tech_contact_id:id_contact}]);
      }
  }
</script>

However, when i'm running this script with all the records (200 000), the script is running well but is able to add only 10 600 records into the target DE. It correspond to an iteration on 2500 records of the first DE.

Can someone help me out to overcome the limit in number of record we can iterate in SSJS or if i'm doing it the wrong way ?

Thank you in advance for your findings and answers.

Answer 1

The SSJS Rows.Retrieve function is only able to retrieve 2500 rows as mentioned in the documentation:

Retrieves up to 2500 rows of data in a data extension

(Source: Rows.Retrieve)

To get around this, you can use WSProxy, as one of the returned properties is called "HasMoreRows", which indicates, that you can fetch another batch of rows.

This is a basic code example from the documentation, that can easily be adapted to retrieve data extension rows instead of emails like in the example:

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);

Related documentation:

• Use Server-Side JavaScript via WSProxy with the SOAP API
• Advanced Retrieves via WSProxy
• Rows.Retrieve

Answer 2

In addition to your answer Markus, please find below the script put in place to overcome the limit of 2500 records retrieve from a data extension :

    <script runat="server">

Platform.Load("core", "1.1.1");

var records = retrieveAllRecords("Name");

var targetDE = DataExtension.Init("Customer key of target DE");

-- Your treatment 


function retrieveFieldNames(name) {

    var attr = DataExtension.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: name });

    var de = DataExtension.Init(attr[0].CustomerKey);

    var fields = de.Fields.Retrieve();

    fields.sort(function (a, b) { return (a.Ordinal > b.Ordinal) ? 1 : -1 });

    var out = [];

    for (k in fields) {
        out = out.concat(fields[k].Name);
    }

    return out;

}

function retrieveAllRecords(name) {

    var prox = new Script.Util.WSProxy();

    var cols = retrieveFieldNames(name);

    var config = {
        name: name,
        cols: cols
    }

    var records = [],
        moreData = true,
        reqID = data = null;

    while (moreData) {

        moreData = false;

        if (reqID == null) {
            data = prox.retrieve("DataExtensionObject[" + config.name + "]", config.cols);
        } else {
            data = prox.getNextBatch("DataExtensionObject[" + config.name + "]", reqID);
        }

        if (data != null) {
            moreData = data.HasMoreRows;
            reqID = data.RequestID;
            for (var i = 0; i < data.Results.length; i++) {
                var result_list = data.Results[i].Properties;
                var obj = {};
                for (k in result_list) {
                    var key = result_list[k].Name;
                    var val = result_list[k].Value
                    if (key.indexOf("_") != 0) obj[key] = val;
                }
                records.push(obj);
            }
        }
    }
    return records;
}
</script>