0

I've some problem when I use the WSProxy in Script Activity. My goal is to search in all the non-sendable data extension if there is a column called "ID" and in the case it's exist I want to remove some ID that I import from another DE. My problem is that when I try to use this script all works fine until I try to import more then 10k records to remove. In this case runtime error occur. Since in the final scenario I have to retrieve millions "ID" I would like to know if there is a more efficient way to do what i want to do or some trick to simplify my code.

Thanks a lot.

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

var InventoryDE = DataExtension.Init("Customer Key of the DE"); // In this DE is stored a list of all non-sendable Data Extensions in the BU

var config = {
    name: "ListIDsToDelete",
    cols: ["ID"]
}


//Retrieve of the IDs that i wanna search and delete from the non-sendable DEs
var records = retrieveRecords(config);

//Retrieve of the single non-sendable DE
var fieldsInventory = InventoryDE.Rows.Retrieve();
var indexOfToRemoveDE = [];


for (i=0;i<fieldsInventory.length(); i++) {
indexOfToRemoveDE[i] = 0
}

var ii = 0;

//Loop on the non-sendable DE
for(i=0; i<fieldsInventory.length(); i++) {
   //If there is in this non-sendable DE a column named "ID" I store the CustomerKey of that DE
   if(fieldsInventory[i].Fields.indexOf('ID') > -1) {
      indexOfToRemoveDE[ii] = fieldsInventory[i].CustomerKey;
      ii++;
   }
}

var jj = 0;

//Loop on the DEs whit a column named "ID" and deletion of the records with an ID equals to one of that retrieved with the WSProxy
for(i=0; i<ii; i++) {
    var dummyDE = DataExtension.Init(indexOfToRemoveDE[i]);
    for(j=0; j<100; j++) {
        dummyDE.Rows.Remove(["ID"], [records[j].ID]);
        jj++;
    }
}


function retrieveRecords(config) {

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

var records = [],
    moreData = true,
    numItems = 0,
    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;
                numItems++;
            }
            records.push(obj);
        }
    }
}
return records;
}

 </script>   
2
  • Just to point out SSJS has a timeout of 30mins
    – EazyE
    Jun 24, 2021 at 12:52
  • How often would you like tor repeat this operation on those 10 million records? Jun 25, 2021 at 7:02

0

You must log in to answer this question.

Browse other questions tagged .