How to Delete Query Activities in Bulk Using SSJS in Salesforce Marketing Cloud

If you’ve worked in Salesforce Marketing Cloud Engagement long enough, you’ve probably accumulated hundreds, or even thousands, of Query Activities. Many were used once. Some were just for testing. And eventually, someone will say it: “We need to clean up unused query definitions.”

Cleaning them up manually in Automation Studio is painful. There’s no native bulk delete. Even in Contact Builder, where you can delete Data Extensions in batches of 200, it quickly becomes inefficient when you’re dealing with thousands of records.

You may have already guessed it this is the time where SSJS comes in to the rescue.

Retrieve query definitions using SSJS

Before we delete something we need to get list of query definitions we can play with and use it for our deletion process. This can’t be done any other way than with SSJS. Wasn’t this supposed to be a low/no-code platform? It feels like we end up writing more code than not. Anyway…

<script runat="server">
    Platform.Load("core", "1.1.1");
    var prox = new Script.Util.WSProxy();

    var cols = ["ObjectID", "Name", "CustomerKey"];
    var filter = {
            Property: "Name",
        SimpleOperator: "like",
        Value: "TEMP_%"
    };

    var moreData = true;
    var reqID = null;
    var results = [];

    while (moreData) {
        var response = prox.retrieve("QueryDefinition", cols, filter, null, reqID);

        if (response && response.Results) {
            results = results.concat(response.Results);
        }

        moreData = response.HasMoreRows;
        reqID = response.RequestID;
    }

    Write(Stringify(results));
</script>

This example fetches all Query Activities starting with TEMP_.

Remove query definitions using SSJS

Before you connect the two script make sure you query the correct data that will be moved for

<script runat="server">
    Platform.Load("Core", "1.1.1");
    var api = new Script.Util.WSProxy(),
        res, 
        batch = [],
        toRemove = 
        [""]
    try{
        
        for (var i = 0; i < toRemove.length; i++){
                Write("Removing " + toRemove[i] +  " ...... " );
                res = QueryDefinition.Init(toRemove[i].substring(0, 36)).Remove();
                Write(res);
                Write("<br>");
           
        }
    }catch(e){
        Write(Stringify(e.Description));
    }
</script>

Alternatively you can use (new Script.Util.WSProxy()).deleteItem("QueryDefinition",{ObjectID: "xxx-xxx-xxx-xxx"}) but it did not worked for me and have used the version above.

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

var prox = new Script.Util.WSProxy();

// assume results[] already populated

for (var i = 0; i < results.length; i++) {

    var deletePayload = {
        ObjectID: results[i].ObjectID
    };

    var deleteResponse = prox.deleteItem("QueryDefinition", deletePayload);

    Write("Deleted: " + results[i].Name + "<br>");
}
</script>

You can build a full-fledged solution where you regularly store query definitions in a Data Extension and use it as your source of truth to decide which Query Activities should be removed.