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!