[SalesForce] How to iterate on more than 2500 records in a DE with SSJS

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 :

Before the treatment :

Become :

After the treatment

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.

Best Answer

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:

Related Topic