Creating Query Definitions in SFMC Made Easy

ByMax Chu

In the last post, we walked through how to clone a Data Extension from a template using WSProxy. That solves half the problem — you’ve got a properly structured target. The other half is getting data into it, and one of the most common ways to do that in SFMC is a Query Activity: a stored SQL query that runs against your data views and writes results into a Data Extension.

Query Activities can be created by hand in Automation Studio, but if you’re programmatically creating Data Extensions per campaign, you almost certainly want to programmatically create the query that populates them too. That’s what today’s script does: it makes sure a query folder exists, then creates a QueryDefinition targeting the Data Extension from the earlier post.

What the script needs to do

  1. Confirm the parent folder for queries exists, and can hold child folders.
  2. Check whether a folder for this specific query already exists under that parent; create it if not.
  3. Build a QueryDefinition object — SQL text, target DE, update behaviour — and create it via QueryDefinition.Add.
  4. Report what happened.

Example script

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

try {
    var parentFolderName = "Query Parent Folder Name";
    var newFolderName = "New Folder Name";
    var targetDEName = "Target Data Extension Name";
    var newQueryName = "New Query Name";
    var campaignCode = "Campaign Code";
    var description = "Automated query for campaign " + campaignCode;

    var msg = [];

    var folders = Folder.Retrieve({
        Property: "Name",
        SimpleOperator: "equals",
        Value: parentFolderName
    });

    if (!folders || folders.length === 0) {
        Write("Error: parent folder '" + parentFolderName + "' was not found.");
    } else {
        var parentFolderId = folders[0].ID;

        // Ensure the parent folder can hold child folders
        if (folders[0].AllowChildren == false) {
            var folder = Folder.Init(parentFolderId);
            folder.Update({
                "ID": parentFolderId,
                "AllowChildren": true,
                "AllowChildrenSpecified": true,
                "IsActive": true,
                "IsEditable": true
            });
        }

        // Check whether the query folder already exists under this parent
        var existingFolder = Folder.Retrieve({
            LeftOperand: {
                Property: "Name",
                SimpleOperator: "equals",
                Value: newFolderName
            },
            LogicalOperator: "AND",
            RightOperand: {
                Property: "ParentFolder.ID",
                SimpleOperator: "equals",
                Value: parentFolderId
            }
        });

        var newFolderID;

        if (!existingFolder || existingFolder.length === 0) {
            var folderGUID = Platform.Function.GUID();
            var newFolder = {
                "Name": newFolderName,
                "CustomerKey": folderGUID,
                "Description": "",
                "ContentType": "queryactivity",
                "IsActive": true,
                "IsEditable": true,
                "AllowChildren": true,
                "ParentFolderID": parentFolderId
            };

            var folderStatus = Folder.Add(newFolder);
            if (folderStatus == "OK") {
                var createdFolder = Folder.Retrieve({
                    Property: "CustomerKey",
                    SimpleOperator: "equals",
                    Value: folderGUID
                });
                newFolderID = createdFolder[0].ID;
                msg.push("Query folder '" + newFolderName + "' was created.");
            } else {
                msg.push("Failed to create query folder '" + newFolderName + "'. Status: " + folderStatus);
            }
        } else {
            newFolderID = existingFolder[0].ID;
            msg.push("Query folder '" + newFolderName + "' already exists.");
        }

        if (newFolderID) {
            // Skip creation if a query with this name already exists
            var existingQuery = QueryDefinition.Retrieve({
                Property: "Name",
                SimpleOperator: "equals",
                Value: newQueryName
            });

            if (existingQuery && existingQuery.length > 0) {
                msg.push("Query '" + newQueryName + "' already exists. Skipping creation.");
            } else {
                var queryDef = {
                    Name: newQueryName,
                    CustomerKey: newQueryName,
                    CategoryID: newFolderID,
                    Description: description,
                    TargetUpdateType: "Overwrite",
                    TargetType: "DE",
                    Target: {
                        Name: targetDEName,
                        CustomerKey: targetDEName
                    },
                    QueryText: "SELECT * FROM Campaign WHERE CampaignCode = '" +
                        campaignCode.replace(/'/g, "''") + "'"
                };

                var queryStatus = QueryDefinition.Add(queryDef);

                if (queryStatus == "OK") {
                    msg.push("Query '" + newQueryName + "' was created.");
                } else {
                    msg.push("Failed to create query '" + newQueryName + "'. Status: " + queryStatus);
                }
            }
        } else {
            msg.push("Could not create or locate query folder '" + newFolderName + "'.");
        }

        Write(msg.join("<br>"));
    }
} catch (e) {
    Write("Unexpected error: " + Stringify(e));
}
</script>
Code language: HTML, XML (xml)

A few things worth knowing if you build on this

  • QueryDefinition.Add validates the object, not the SQL. Always test run a new query manually in Automation Studio (or via QueryDefinition.Execute in a controlled test) at least once before trusting an automated pipeline to run it unattended.
  • TargetUpdateType has real consequences. “Overwrite” truncates the target DE on every run — appropriate for a fully-rebuilt campaign snapshot, dangerous if you actually wanted to accumulate history. “Append” and “Update” (upsert, based on primary key) are the other options; pick deliberately, not by default.
  • The query targets the DE by Name/CustomerKey, not by category. If two Data Extensions in different folders share a CustomerKey, Target resolution can become ambiguous. Keeping CustomerKey values unique across your whole account (not just within a folder) avoids this entirely.

Wrapping up

Together, these two scripts cover a common SFMC automation pattern end to end: clone a Data Extension from a governed template, then stand up the query that populates it — both idempotent, both safe to re-run, and both reporting clear status instead of failing silently. From here, the natural next step is wiring both into a single Automation Studio program, so a new campaign can go from “give me a folder and a campaign code” to “Data Extension built and populated” without anyone touching Content Builder by hand.

Similar Posts