This article explains how to delete multiple Data Extension records with a Script Activity in a Marketing Cloud Automation.
Problem
When it comes to processing thousands of records in a Marketing Cloud Data Extension, SQL is king!
But what we can do with those records is very limited, because DELETE, UPDATE and INSERT statements are not supported by the SQL Query activities.
Thankfully, server-side JavaScript can help us bridge that gap and allow us to perform these actions to up to 100.000 records at a time, thanks to the Script activities.
Use case
For the purpose of this article, let’s consider a use case, where a merge between 2 Data Extensions went terribly wrong and we urgently need to delete several thousands of records.
Here is the structure of the Data Extension, named “MyDataExtension“:
| Field name | Primary Key | Type | Length |
| SubscriberKey | Yes | Text | 50 |
| Campaign | Yes | Text | 254 |
| Segment | No | Text | 50 |
| CreatedDate | No | Date |
What we know is that the corrupted records either have the wrong Campaign name or the wrong Segment and CreatedDate values.
Solution
Since we are building a Script activity, we are going to use the code from my previous article, that takes into account the 30 minute expiration and error handling.
But instead of processing the data and updating it in the original Data Extension, we are going to create a function to delete the records we retrieved.
Let’s have a look at 3 key elements that are different and examine them separately:
#1 Configuration object
As previously stated, we need a complex filter to extract the records that either have the wrong Campaign name or the wrong Segment and CreatedDate values.
This object holds all the information we need for our retrieve request and error logging.
var config = {
de: "MyDataExtension",
filter: {
LeftOperand: {
LeftOperand: {
Property: "Segment",
SimpleOperator: "equals",
Value: "B2B"
},
LogicalOperator: "AND",
RightOperand: {
Property: "CreatedDate",
SimpleOperator: "greaterThan",
Value: "2020-06-12"
}
},
LogicalOperator: "OR",
RightOperand: {
Property: "Campaign",
SimpleOperator: "equals",
Value: "June2021"
}
},
error: {
de: "Automation ErrorLog",
source: "Script Activity #1"
}
}#2 Create batch function for deletion
In order to create a batch object for deleting records using the WSProxy method, there are 2 main requirements:
- We need to provide the values for all the primary keys.
- We need to use the name Keys instead of Properties to pass the Name/Value pairs to WSProxy.
function createBatches(key, records, primaries) {
var batches = [];
for (var k in records) {
var keys = {};
for(var i = 0; i < primaries.length; i++) {
var primary = primaries[i];
keys[primary] = records[k][primary];
}
batches.push({
CustomerKey: key,
Keys: wsPack(keys)
});
}
return batches;
}#3 Delete function
If the deletion batches were compiled correctly, this function will use the WSProxy method to delete all the records one by one without using any JavaScript loops.
function deleteRecords(batches) {
return api.deleteBatch("DataExtensionObject", batches);
}Full code
Now, let’s have a look at the full code.
<script runat="server">
Platform.Load("core", "1");
var api = new Script.Util.WSProxy();
var config = {
de: "MyDataExtension",
filter: {
LeftOperand: {
LeftOperand: {
Property: "Segment",
SimpleOperator: "equals",
Value: "B2B"
},
LogicalOperator: "AND",
RightOperand: {
Property: "CreatedDate",
SimpleOperator: "greaterThan",
Value: "2020-06-12"
}
},
LogicalOperator: "OR",
RightOperand: {
Property: "Campaign",
SimpleOperator: "equals",
Value: "June2021"
}
},
error: {
de: "Automation ErrorLog",
source: "Script Activity #1"
}
}
try {
var result = retrieveAttributes(config);
result.records = retrieveRecords(result.customerKey, result.columns.all, config.filter);
result.records = formatRecords(result.records, result.columns);
result.count = result.records.length;
result.batches = createBatches(result.customerKey, result.records, result.columns.prims);
result.deleted = deleteRecords(result.batches);
if(result.deleted.Status == "OK") {
Write(Stringify(result));
} else {
throw result.deleted;
}
} catch(err) {
logError(err, config);
}
function logError(err, config) {
var props = {
Id: GUID(),
Message: Stringify(err.message),
Description: Stringify(err.description),
Source: config.error.source,
CreatedDate: DateTime.SystemDateToLocalDate(Now())
}
err.log = api.createItem("DataExtensionObject", {
Name: config.error.de,
Properties: wsPack(props)
});
if(Platform.Request.RequestURL() != null) {
Write(Stringify(err));
} else {
createAutomationError();
}
}
function deleteRecords(batches) {
return api.deleteBatch("DataExtensionObject", batches);
}
function createBatches(key, records, primaries) {
var batches = [];
for (var k in records) {
var keys = {};
for(var i = 0; i < primaries.length; i++) {
var primary = primaries[i];
keys[primary] = records[k][primary];
}
batches.push({
CustomerKey: key,
Keys: wsPack(keys)
});
}
return batches;
}
function formatRecords(records, columns) {
for(var k in records) {
var record = records[k];
for(var name in record) {
if(record[name].length > 0) {
if(inArray(columns.dates, name)) record[name] = new Date(record[name]);
if(inArray(columns.nums, name)) record[name] = Number(record[name]);
if(inArray(columns.decs, name)) record[name] = Number(record[name]);
if(inArray(columns.bools, name)) record[name] = (record[name].toLowerCase() == "true") ? true : false;
}
}
}
return records;
}
function retrieveRecords(key, cols, filter) {
var now = new Date(),
start = now.getTime(),
timeOut = (60000 * 15); // 15 minutes
var records = [],
moreData = true,
reqID = data = null;
while (moreData && (new Date().getTime() - start) < timeOut) {
moreData = false;
if (reqID == null) {
data = api.retrieve("DataExtensionObject[" + key + "]", cols, filter);
} else {
data = api.getNextBatch("DataExtensionObject[" + key + "]", reqID);
}
if (data != null) {
moreData = data.HasMoreRows;
reqID = data.RequestID;
for (var i = 0; i < data.Results.length; i++) {
var props = data.Results[i].Properties;
records.push(wsUnpack(props));
}
}
}
if(records.length === 0) throw "No records could be found!";
return records;
}
function retrieveAttributes(config) {
var req = DataExtension.Retrieve(
{
Property: "Name",
SimpleOperator: "equals",
Value: config.de
}
);
if(req[0].CustomerKey != null) {
var customerKey = req[0].CustomerKey;
} else {
throw "The DataExtension \"" + config.de + "\" could not be found!";
}
var de = DataExtension.Init(customerKey);
var fields = de.Fields.Retrieve();
fields.sort(function (a, b) {
return (a.Ordinal > b.Ordinal) ? 1 : -1
});
var columns = [],
dates = [],
bools = [],
nums = [],
decs = [],
prims = [];
for (var k in fields) {
var field = fields[k];
columns.push(field.Name);
if(field.FieldType == "Date") dates.push(field.Name);
if(field.FieldType == "Number") nums.push(field.Name);
if(field.FieldType == "Boolean") bools.push(field.Name);
if(field.FieldType == "Decimal") decs.push(field.Name);
if(field.IsPrimaryKey == true) prims.push(field.Name);
}
return {
name: config.de,
customerKey: customerKey,
fields: fields,
columns: {
all: columns,
dates: dates,
bools: bools,
nums: nums,
decs: decs,
prims: prims
}
};
}
function inArray(arr, k) {
var out = false;
for (var i in arr) {
if (arr[i] == k) out = true;
}
return out;
}
function wsPack(obj) {
var out = [];
for (k in obj) {
out.push({
Name: k,
Value: obj[k]
});
}
return out;
}
function wsUnpack(props) {
var out = {};
for(var k in props) {
var item = props[k];
out[item.Name] = item.Value;
}
return out;
}
</script>Result
Here is what the output looks like if you run the code from a Cloud page.
{
"name": "MyDataExtension",
"customerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
"fields": [
{
"Name": "SubscriberKey",
"ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-f5e54415536f",
"FieldType": "Text",
"IsPrimaryKey": true,
"MaxLength": 18,
"Ordinal": 0,
"DefaultValue": "",
"StorageType": "Plain"
},
{
"Name": "Campaign",
"ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-1792e9c0c423",
"FieldType": "Text",
"IsPrimaryKey": false,
"MaxLength": 255,
"Ordinal": 2,
"DefaultValue": "",
"StorageType": "Plain"
},
{
"Name": "Segment",
"ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-3b52d27bf806",
"FieldType": "Text",
"IsPrimaryKey": false,
"MaxLength": 255,
"Ordinal": 2,
"DefaultValue": "",
"StorageType": "Plain"
},
{
"Name": "CreatedDate",
"ObjectID": "xxxxxxxx-xxxx-xxxx-xxxx-6d07755a6281",
"FieldType": "Date",
"IsPrimaryKey": false,
"MaxLength": 0,
"Ordinal": 62,
"DefaultValue": "GETDATE()",
"StorageType": "Plain"
}
],
"columns": {
"all": [
"SubscriberKey",
"Campaign",
"Segment",
"CreatedDate"
],
"dates": [
"CreatedDate"
],
"bools": [],
"nums": [],
"decs": [],
"prims": [
"SubscriberKey",
"Campaign"
]
},
"records": [
{
"SubscriberKey": "XXXXXXXXXXXXXXXPQAR",
"Campaign": "June2021",
"Segment": "B2B",
"CreatedDate": "2021-08-12T09:18:02.000"
},
{
"SubscriberKey": "XXXXXXXXXXXXXXXxQAN",
"Campaign": "June2021",
"Segment": "B2B",
"CreatedDate": "2021-08-12T09:18:02.000"
},
{
"SubscriberKey": "XXXXXXXXXXXXXXXYQAV",
"Campaign": "June2021",
"Segment": "B2B",
"CreatedDate": "2021-08-12T09:18:02.000"
}
],
"count": 3,
"batches": [
{
"CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
"Keys": [
{
"Name": "SubscriberKey",
"Value": "XXXXXXXXXXXXXXXPQAR"
},
{
"Name": "Campaign",
"Value": "June2021"
}
]
},
{
"CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
"Keys": [
{
"Name": "SubscriberKey",
"Value": "XXXXXXXXXXXXXXXxQAN"
},
{
"Name": "Campaign",
"Value": "June2021"
}
]
},
{
"CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
"Keys": [
{
"Name": "SubscriberKey",
"Value": "XXXXXXXXXXXXXXXYQAV"
},
{
"Name": "Campaign",
"Value": "June2021"
}
]
}
],
"deleted": {
"Status": "OK",
"RequestID": "xxxxxxxx-xxxx-xxxx-xxxx-0d21bb23b5ac",
"Results": [
{
"ErrorMessage": null,
"KeyErrors": null,
"Object": {
"CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
"Properties": null,
"Name": null,
"Keys": [
{
"Name": "SubscriberKey",
"Value": "XXXXXXXXXXXXXXXPQAR"
},
{
"Name": "Campaign",
"Value": "June2021"
}
],
"Type": null,
"Client": null,
"PartnerKey": null,
"PartnerProperties": null,
"CreatedDate": "0001-01-01T00:00:00.000",
"ModifiedDate": null,
"ID": 0,
"ObjectID": null,
"Owner": null,
"CorrelationID": null,
"ObjectState": null,
"IsPlatformObject": false
},
"StatusCode": "OK",
"StatusMessage": "Deleted DataExtensionObject",
"OrdinalID": 0,
"ErrorCode": 0,
"RequestID": null,
"ConversationID": null,
"OverallStatusCode": null,
"RequestType": "Synchronous",
"ResultType": null,
"ResultDetailXML": null
},
{
"ErrorMessage": null,
"KeyErrors": null,
"Object": {
"CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
"Properties": null,
"Name": null,
"Keys": [
{
"Name": "SubscriberKey",
"Value": "XXXXXXXXXXXXXXXxQAN"
},
{
"Name": "Campaign",
"Value": "June2021"
}
],
"Type": null,
"Client": null,
"PartnerKey": null,
"PartnerProperties": null,
"CreatedDate": "0001-01-01T00:00:00.000",
"ModifiedDate": null,
"ID": 0,
"ObjectID": null,
"Owner": null,
"CorrelationID": null,
"ObjectState": null,
"IsPlatformObject": false
},
"StatusCode": "OK",
"StatusMessage": "Deleted DataExtensionObject",
"OrdinalID": 1,
"ErrorCode": 0,
"RequestID": null,
"ConversationID": null,
"OverallStatusCode": null,
"RequestType": "Synchronous",
"ResultType": null,
"ResultDetailXML": null
},
{
"ErrorMessage": null,
"KeyErrors": null,
"Object": {
"CustomerKey": "xxxxxxxx-xxxx-xxxx-xxxx-4BFBDA53FC1E",
"Properties": null,
"Name": null,
"Keys": [
{
"Name": "SubscriberKey",
"Value": "XXXXXXXXXXXXXXXYQAV"
},
{
"Name": "Campaign",
"Value": "June2021"
}
],
"Type": null,
"Client": null,
"PartnerKey": null,
"PartnerProperties": null,
"CreatedDate": "0001-01-01T00:00:00.000",
"ModifiedDate": null,
"ID": 0,
"ObjectID": null,
"Owner": null,
"CorrelationID": null,
"ObjectState": null,
"IsPlatformObject": false
},
"StatusCode": "OK",
"StatusMessage": "Deleted DataExtensionObject",
"OrdinalID": 2,
"ErrorCode": 0,
"RequestID": null,
"ConversationID": null,
"OverallStatusCode": null,
"RequestType": "Synchronous",
"ResultType": null,
"ResultDetailXML": null
}
]
}
}Conclusion
Although this method seems overly complicated, us developers, we don’t have much of a choice when it comes to bridging the gaps between languages.
Let’s hope that one day Marketing Cloud developers will hear our prayers and we won’t have to write hundreds of lines of code to replace a simple SQL statement that should have been there in the first place.
Have I missed anything?
Please poke me with a sharp comment below or use the contact form.
Can we use the full code on script activity in automation studio ?
try it 😉