Automate Data Extension Creation in SFMC

ByMax Chu

If you build automations in Salesforce Marketing Cloud (SFMC), you’ve probably hit this workflow: you have a “template” Data Extension with a carefully designed schema — the right field types, primary keys, retention settings — and you need to spin up a new Data Extension with that exact same structure, over and over, for different campaigns or business units.

Doing this by hand in the UI is tedious and error-prone. The good news is that Marketing Cloud’s Server-Side JavaScript (SSJS) API gives you everything you need to automate it: retrieve a template DE’s field definitions, retrieve or create a target folder, and create a brand-new DE from that structure via the SOAP-backed WSProxy object.

Below, I’ll walk through a script that does exactly this, and share a cleaned-up implementation you can drop into a Script Activity or Cloud Page.

What the script needs to do

  1. Look up a target folder by name in Content Builder / Data Extension folders.
  2. If that folder can’t currently hold child folders or assets, flip AllowChildren on.
  3. Read the field schema from a template Data Extension.
  4. Create a new Data Extension in the target folder using that schema, with sensible defaults for sendable status and data retention.
  5. Report success or failure back to whoever ran the script.

Example script

<script runat="server">
Platform.Load("core", "1.1.5");
try {
    var parentFolderName = "Data Extension Parent Folder Name";
    var deName = "New Data Extension Name";
    var templateDEKey = "Data_Extension_Template";
    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 folder can actually hold new assets
        if (folders[0].AllowChildren == false) {
            var folder = Folder.Init(parentFolderId);
            folder.Update({
                "ID": parentFolderId,
                "AllowChildren": true,
                "AllowChildrenSpecified": true,
                "IsActive": true,
                "IsEditable": true
            });
        }
        // Bail out early if a DE with this name already exists
        var existing = DataExtension.Retrieve({
            Property: "Name",
            SimpleOperator: "equals",
            Value: deName
        });
        if (existing && existing.length > 0) {
            msg.push("Data Extension '" + deName + "' already exists. Skipping creation.");
        } else {
            var status = cloneDataExtension(deName, parentFolderId, templateDEKey);
            if (status == "OK") {
                msg.push("Data Extension '" + deName + "' was created successfully.");
            } else {
                msg.push("Failed to create Data Extension '" + deName + "'. Status: " + status);
            }
        }
        Write(msg.join("<br>"));
    }
} catch (e) {
    Write("Unexpected error: " + Stringify(e));
}

function cloneDataExtension(customerKey, folderId, templateKey) {
    var prox = new Script.Util.WSProxy();
    prox.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });
    var templateDE = DataExtension.Init(templateKey);
    var fields = templateDE.Fields.Retrieve();

    // get the original field sequence
    fields.sort(function (a, b) {
        return a.Ordinal - b.Ordinal;
    });

    for (var i = 0; i < fields.length; i++) {
        if (fields[i]["IsPrimaryKey"] == true) {
            fields[i]["IsRequired"] = true;
        }
    }

    var clonedDE = {
        "CustomerKey": customerKey,
        "Name": customerKey,
        "Description": "Cloned from template: " + templateKey,
        "CategoryID": folderId,
        "Fields": fields,
        "SendableDataExtensionField": {
            "Name": "Subscriber_Key",
            "FieldType": "Text"
        },
        "SendableSubscriberField": {
            "Name": "Subscriber Key"
        },
        "IsSendable": true,
        "DataRetentionPeriodLength": 2,
        "RowBasedRetention": true,
        "ResetRetentionPeriodOnImport": false,
        "DeleteAtEndOfRetentionPeriod": false,
        "DataRetentionPeriodUnitOfMeasure": 6,
        "DataRetentionPeriod": "Years"
    };
    var res = prox.createItem("DataExtension", clonedDE);
    return res.Results[0].StatusCode;
}
</script>
Code language: HTML, XML (xml)

A few things worth knowing if you build on this

  • Retention settings are template-specific. The two-year, row-based retention policy in this script is a default — make sure it actually matches your organisation’s data governance policy before reusing this in production. Retention settings that silently delete data after the wrong interval are a common source of “where did my subscriber history go?” tickets.
  • IsSendable: true requires a valid SendableDataExtensionField. If your template’s primary subscriber-key field isn’t named Subscriber_Key, update that mapping, or the create call will fail.
  • WSProxy vs. the simplified DataExtension object. This script uses WSProxy.createItem specifically because the simplified DataExtension.Init(…).CreateObject(…) style API doesn’t give you full control over every property (like custom retention settings) in one call. If your use case is simpler — no custom retention, no sendable config — the simplified object model requires less boilerplate.

Wrapping up

Data Extension templating is one of those unglamorous automations that pays for itself the first time you save someone from manually recreating a 40-field schema for the fifth time this quarter. The pattern here — retrieve template fields, validate the destination, clone with WSProxy — generalises well beyond this specific script, and it’s worth having in your SFMC toolbox alongside your AMPscript and Journey Builder work.

Similar Posts