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>