0

ok, I have been working on a small little side project to learn SSJS, but also produce something useful.. I want to create a Master DE that reports some Attributes about the DEs that we have in our BU, but also get their Counts...

So the script works / but when I add the elements of:

Use AMPscript to retrieve the row count  
var rowCountAMPscript = "<script runat='server' language='ampscript'>SET @rowCount = DataExtensionRowCount('" + customerKey + "') OUTPUT(v(@rowCount))</script>";  
  
Execute the AMPscript and retrieve the row count  
var rowCount = Platform.Function.TreatAsContent(rowCountAMPscript); 

it doesn't process all of them and cut off the results, if I remove the AMPScript parts and just not process for RecordCount at all, I get all the DEs listed for their other attributes:

here is my whole Script

<script runat="server">  
    Platform.Load("Core", "1.1");  

    try {  
        // Initialize the output Data Extension where the metadata will be stored  
        var outputDE = DataExtension.Init("List_DE_Metadata"); // Replace with your target DE External Key  
        Write("<p>Output Data Extension initialized...</p>");  
  
        // Define the columns to retrieve  
        var cols = [  
            "Name",  
            "CustomerKey",  
            "CreatedDate",  
            "ModifiedDate",  
            "Description",  
            "CategoryID",  
            "IsSendable" // Add IsSendable property  
        ];  
  
        var moreData = true; // Flag to track if more data is available  
        var reqID = null; // Request ID for pagination  
        var totalCount = 0; // To keep track of total records retrieved  
        var skippedRecords = 0; // To keep track of skipped records  
        var batchSize = 250; // Increase batch size for faster processing  
  
        Write("<p>Starting Data Extension retrieval with pagination...</p>");  
  
        // Loop to retrieve Data Extensions in batches  
        while (moreData) {  
            var filter = null; // No filters applied  
            var opts = { BatchSize: batchSize }; // Retrieve up to 250 DEs per batch  
            var props = { QueryAllAccounts: false }; // Set QueryAllAccounts to false (current BU only)  
  
            if (reqID) {  
                props.ContinueRequest = reqID; // Continue from the last RequestID  
            }  
  
            // Retrieve the next batch of Data Extensions  
            var api = new Script.Util.WSProxy();  
            var req = api.retrieve("DataExtension", cols, filter, opts, props);  
  
            if (req && req.Results && req.Results.length > 0) {  
                moreData = req.HasMoreRows || !!req.RequestID; // Determine if more data is available  
                reqID = req.RequestID; // Save the RequestID for the next batch  
  
                Write("<p>Retrieved " + req.Results.length + " Data Extensions in this batch.</p>");  
  
                for (var i = 0; i < req.Results.length; i++) {  
                    try {  
                        var de = req.Results[i];  
  
                        // Retrieve Data Extension properties  
                        var name = de.Name || "Unknown";  
                        var customerKey = de.CustomerKey || "Unknown";  
                        var createdDate = de.CreatedDate || "Not Available";  
                        var modifiedDate = de.ModifiedDate || "Not Available";  
                        var description = de.Description || "Not Available";  
                        var isSendable = de.IsSendable || false; // Default to false if not provided  

// Use AMPscript to retrieve the row count  
var rowCountAMPscript = "<script runat='server' language='ampscript'>SET @rowCount = DataExtensionRowCount('" + customerKey + "') OUTPUT(v(@rowCount))</script>";  
  
// Execute the AMPscript and retrieve the row count  
var rowCount = Platform.Function.TreatAsContent(rowCountAMPscript);  

                        // Insert the Data Extension metadata into the target Data Extension  
                        try {  
                            outputDE.Rows.Add({  
                                DataExtensionName: name,  
                                CustomerKey: customerKey,  
                                CreatedDate: createdDate,  
                                LastModifiedDate: modifiedDate,  
                                Description: description,  
                                IsSendable: isSendable, // Add IsSendable field  
                                RecordCount: rowCount
                            });  
                            Write("<p>Inserted record for DE: " + name + "</p>");  
                            totalCount++; // Increment total count  
                        } catch (insertError) {  
                            Write("<p>Error inserting row for DE: " + name + "</p>");  
                            Write("<p>Error Details: " + Stringify(insertError) + "</p>");  
                            skippedRecords++; // Increment skipped record count  
                        }  
                    } catch (error) {  
                        Write("<p>Error processing Data Extension: " + req.Results[i].Name + "</p>");  
                        Write("<p>Error Details: " + Stringify(error) + "</p>");  
                        skippedRecords++; // Increment skipped record count  
                    }  
                }  
            } else {  
                moreData = false; // No more data to retrieve  
                Write("<p>No Data Extensions retrieved in this batch.</p>");  
            }  
        }  
  
        // Output final results  
        Write("<p>Total Data Extensions Retrieved: " + totalCount + "</p>");  
        Write("<p>Total Skipped Records: " + skippedRecords + "</p>");  
        Write("<p>Script Execution Completed. All Data Extensions have been processed.</p>");  
    } catch (error) {  
        // Handle any script-level errors  
        Write("<p>Script Error: " + Stringify(error) + "</p>");  
    }  
</script>  

As you can see I tried to use a Batch process, but it doesn't seem to work when the AMPScript is added, is there anyways, to alter this or maybe use a different method, so that 2500 limit (I think that is probably is it) is removed ?

or some other hack to get the counts updated ?

I tried playing around with the batch size, 250 or 500 and doesn't seem to change the cut off results.

thanks!

6
  • are you writing any errors to a errorde to review? Commented Apr 4 at 18:08
  • no, the script has no Error writing to a DE / what you had in mind ? Commented Apr 4 at 18:57
  • 1
    since you have try/catch the SSJS can be failing silenty, you should be writing any error message to a DE so you can review it Commented Apr 4 at 20:00
  • thanks for feedback / will investigate that later, cheers! Commented Apr 4 at 21:21
  • I've implemented the Error DE with extra code / what does this error code mean ? " {"message":"Thread was being aborted.","description":"System.Threading.ThreadAbortException: Thread was being aborted. - from mscorlib\r\n\r\n"} " on DE "SF_EML_KEY_LKP_BY_EMAIL_KEY_OR_EMAIL_ENT_ID" Commented Apr 7 at 19:59

1 Answer 1

0

The 2500 limit is a thing in any lookuprows().

More likely though: DataExtensionRowCount() expects the Data Extension name as most AMPscript tends to; but you are serving it customerkeys. Maybe only some DEs have the same name / customerkey, and those are the ones you see consistently, while the rest is "cut off" (not retrieved)?.

https://developer.salesforce.com/docs/marketing/marketing-cloud-ampscript/references/mc-ampscript-data-extension/mc-ampscript-reference-data-extension-de-row-count.html

3
  • just tried using de.name and it worked and returned more I would say, but still short, got about 2273 back, but there are 3500 records normally without RecordCount Commented Apr 4 at 20:03
  • but I have an idea / I can run this script twice, once for > 0 records and once for 0 to catch them all - I just tested that and it seems to work nicely // but if you have other ideas keep them coming Commented Apr 4 at 20:31
  • // Skip processing if RecordCount is 0 if (parseInt(rowCount, 10) === 0) { Write("<p>Skipped Data Extension '" + name + "' as RecordCount is 0.</p>"); skippedRecords++; // Increment skipped record count continue; // Skip to the next Data Extension } Commented Apr 4 at 20:32

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.