1

I am using the following script (in Automation Studio as a Script Activity) to remove all the rows of the last 30 days from a DE. The automation is run daily. I log errors and messages in a DE called deLogDev.

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

var deLogDev = DataExtension.Init("XXX-EXTERNAL-KEY-1-XXXXXX");
var deBookings = DataExtension.Init("XXX-EXTERNAL-KEY-2-XXXXXX");
var today = new Date();
var thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(today.getDate() - 30);

try
{
    var filter = {Property:"LastDepartureDateTime", SimpleOperator:"greaterThan", thirtyDaysAgo };
    var data = deBookings.Rows.Retrieve(filter);
    for (var i=0; i < data.length; i++) {
      var result = deBookings.Rows.Remove(["LastDepartureDateTime"], [data[i].LastDepartureDateTime]);    
    }
    var rowCount = data.length;
    deLogDev.Rows.Add({Message:Stringify(rowCount) + " items removed from this DE.",Location:"Remove Old Bookings Script",Date:Now()});
}
catch(ex)
{
    deLogDev.Rows.Add({Message:Stringify(ex),Location:"Remove Old Bookings Script",Date:Now()});
}
</script>

What I notice in the log are messages like this every day.

2500 items removed from this DE.

I run the same script for another data extension, but it looks as if the number 2500 is constant. This makes me suspicious because it is unlikely that the number of the rows every day and for two different DE's is exactly the same and always 2500.

UPDATE 1: I just notice that this problem is not unique and according to this question there is a 2500 row limit on what SSJS is able to retrieve. But since the question four years old, I would like to know if there has been any improvement on this issue.

UPDATE 2: I applied the do-while loop logic used in this question to retrieve what's left repeatedly. But my automation fails at this script activity and nothing gets logged in my log DE (deLogDev). This probably means that the error is not caught in the try-catch clause.

Here's what I have. Any idea why it fails?

<script runat="server">
Platform.Load("core","1");
try
{  
    var deLogDev = DataExtension.Init("XXX-EXTERNAL-KEY-1-XXXXXX");
    var deBookings = DataExtension.Init("XXX-EXTERNAL-KEY-2-XXXXXX");
    var today = new Date();
    var thirtyDaysAgo = new Date();
    thirtyDaysAgo.setDate(today.getDate() - 30);
    var rowCount = 0;
    do
    {
      var filter = {Property:"LastDepartureDateTime", SimpleOperator:"greaterThan", thirtyDaysAgo };
      var data = deBookings.Rows.Retrieve(filter);
      var rowCount = data.length;
      for (var i=0; i < rowCount; i++) 
      {
        deBookings.Rows.Remove(["LastDepartureDateTime"], [data[i].LastDepartureDateTime]);    
      }
      deLogDev.Rows.Add({Message:Stringify(rowCount) + " items removed from this DE.", Location:"Remove Old Bookings Script", Date:Now()});    
    } while (rowCount > 0);

}
catch(ex)
{
    deLogDev.Rows.Add({Message:Stringify(ex),Location:"Remove Old Bookings Script",Date:Now()});
}
</script>
  • There is still a 2500 record limit – EazyE Aug 21 '19 at 11:41
  • @EazyE this is bad. Could you please take a look at my UPDATE 2 and see why my do-while loop does not work? – Disasterkid Aug 21 '19 at 12:14
  • Try running it in a microsite or cloud page and see if you can debug the error through there. I would say it is timing out, but if it is not pushing anything in the logs, then it sounds like there is some sort of syntax error or incorrect key setting, etc.that prevents it from reaching that part of your script. – Gortonington Aug 21 '19 at 13:54
  • @Gortonington could you briefly explain how to test it on a cloud page in a way that the code is not exposed? – Disasterkid Aug 22 '19 at 10:32
  • You put the script you have in your script activity onto a cloud page and then just add outputs (Writes) to push the content you want to verify while you test so you can view the results. I am not sure what you mean about the 'code being exposed' This is all SSJS so it is only viewed on server, not client-side - so the code is never shown. – Gortonington Aug 22 '19 at 13:10
2

Rows.Retrieve only retrieves 2500 records per documentation: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/ssjs_dataExtensionRowsRetrieve.htm

You can use SSJS API Functions to do a RetrieveRequest and ContinueRequest. It is not that well documented in my option of how to do this.

API Functions: https://developer.salesforce.com/docs/atlas.en-us.mc-programmatic-content.meta/mc-programmatic-content/ssjs_platformAPIFunctions.htm

RetrieveRequest: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-apis.meta/mc-apis/retrieverequest.htm?search_text=retrieverequest

Example how do it in .NET and PHP. Can't find anything regarding SSJS though. https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-apis.meta/mc-apis/continuerequest_2500records.htm

Code Example:

<script runat=server>

Platform.Load('Core', '1.1.1');

var rr =    ('RetrieveRequest');
Platform.Function.SetObjectProperty(rr, 'ObjectType', 'DataExtensionObject[DE_KEY]'); 
Platform.Function.AddObjectArrayItem(rr, 'Properties', 'Attribute1');
Platform.Function.AddObjectArrayItem(rr, 'Properties', 'Attribute2');


    var results = [0,0];
    var rows = Platform.Function.InvokeRetrieve(rr, results);
    var status = results[0];
    var requestId = results[1];

    do {
    if (rows != null) {
      for (var i in rows) {
        var attribute1 = rows[i].Properties[0].Value;
        var attribute2 = rows[i].Properties[1].Value;

        //do stuff
      }
    }
    rr.ContinueRequest = requestId;
    }
    while(status === "MoreDataAvailable");

</script>
| improve this answer | |
  • Could you please check at UPDATE 2? I wonder if the do-while loop works. I'd rather not change my entire solution just yet. – Disasterkid Aug 21 '19 at 12:13
  • 2
    It sounds like it has worked before. But in the same time it looks like you have a syntax error when creating the filter variable. For the last argument you should have Value: I.e. var filter = {Property:"LastDepartureDateTime", SimpleOperator:"greaterThan", Value: thirtyDaysAgo }; – user3274635 Aug 21 '19 at 12:50
  • Oh, thank you so much! I corrected that. The removing seems to be in progress judging by the log. I am waiting to see if it truly works. The progress is quite slow. – Disasterkid Aug 21 '19 at 13:24
  • I want to note that @user3274635 answer is best practice for most of these types of needs as the only reason your solution works is that the records are being deleted from the retrieve DE. Any other solution would require the ContinueRequest method. – Gortonington Aug 21 '19 at 13:56
  • 1
    I can also add that @Disasterkids solution is a bit scary. If, for some reason a record fails to delete and not throwing an error. You could get stuck in an endless loop. – user3274635 Aug 21 '19 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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