Create data extensions in bulk using SSJS
Imagine you need to create a lot of data extensions. Let’s not overcomplicate things and just say we will have the same fields added to all of them. What is the number of data extensions you’d be willing to create manually? Or, where is the line where you’d say, “I’d better let a script do it, or else I’ll definitely get carpal tunnel from doing this”?
For me, the number is definitely above 10. But in this case, it doesn’t matter because the number of data extensions I needed to create was close to 800. That’s a number nobody—especially with a user-friendly interface like Marketing Cloud—wants to handle manually by copying.
Preparation
Here are some things we need to prepare beforehand. How do we name the data extensions we create in bulk? Should we organize them by country or use another naming pattern? It’s important to get that sorted first. Also, for future reference, such as when referencing a data extension in a query definition, it’s a good idea to make the customer key and name something we can easily reference them anywhere using our predefined pattern.
For our example we will start of the script for creating data extension using SSJS. And use WS proxy as that is our go to function to basically do anything advanced in Marketing Cloud Engagement. WS proxy is a wrapper to
Script
<script runat="server">
Platform.Load("core", "1");
var api = new Script.Util.WSProxy();
try {
api.setClientId({"ID": Platform.Function.AuthenticatedMemberID()});
var fields = [
{
"Name": "SubscriberKey",
"FieldType": "Text",
"MaxLength": 50,
"IsPrimaryKey": true,
"IsRequired" : true
},
{
"Name": "FirstName",
"FieldType": "Text",
"MaxLength": 50
},
{
"Name": "LastName",
"FieldType": "Text",
"MaxLength": 80
},
{
"Name": "EmailAddress",
"FieldType": "EmailAddress"
}
];
var config = {
"CustomerKey": GUID(),
"Name": "MyDataExtension",
"CategoryID": 1234,
"Fields": fields,
"SendableDataExtensionField": {
"Name" : "SubscriberKey",
"FieldType" : "Text"
},
"SendableSubscriberField": {
"Name": "Subscriber Key"
},
"IsSendable": true,
"IsTestable": true,
"DataRetentionPeriodLength": 7,
"DataRetentionPeriod": "Days",
"RowBasedRetention": 0,
"ResetRetentionPeriodOnImport": 1,
"DeleteAtEndOfRetentionPeriod": 0
};
var result = api.createItem("DataExtension", config);
Write(Stringify(result));
} catch(error) {
Write(Stringify(error));
}
</script>Detailed script explanation
Platform.Load(“core”, “1”): This loads the core SSJS library in Marketing Cloud, which is necessary for executing functions like Platform.Function.AuthenticatedMemberID().
WSProxy: var api = new Script.Util.WSProxy(); initializes the WSProxy object. WSProxy is a more efficient and simplified way of interacting with the SOAP API in Marketing Cloud. It wraps around SOAP methods to make it easier to perform tasks like creating or updating data extensions, subscribers, etc.
api.setClientId(): This line sets the client context, using the AuthenticatedMemberID to ensure the script operates under the correct business unit.
Field Definitions (fields array): The fields array defines the structure of each data extension. In this example:
"SubscriberKey"is a text field, primary key, and required."DateAdded"is a date field, optional."EmailAddress"is an email field.
You can add more fields here if needed for your specific DE structure.
Settings (settings array): The settings array contains multiple configurations for creating different sets of data extensions. Each setting has:
"namePrefix": A prefix (like “AA”, “BB”, etc.) that will be part of the DE name."folderId": The ID of the folder where the data extensions will be created.
getCombinedNames Function: This function generates the names of the data extensions by combining the namePrefix (from settings) with a list of commonNames (like “DD”, “EE”, etc.). For example, if the prefix is “AA”, the resulting DE names will be ["AA_DD", "AA_EE", "AA_GG", "AA_LL"].
Main Loop: There are two nested loops:
- Outer loop (
for (var j = 0; j < settings.length; j++)) goes through each configuration insettings. For example, it first uses thenamePrefix“AA” andfolderId581025. - Inner loop (
for (var i = 0; i < dataExtensions.length; i++)) iterates over the combined names generated bygetCombinedNames. For each combination, a new DE is created.
Creating Data Extensions: For each combination, a config object is created, which defines how the data extension should be configured:
- CustomerKey: A unique key for each data extension, generated by taking the MD5 hash of the DE name and truncating it to 36 characters.
- Name: The actual name of the DE.
- Fields: The field structure (from the
fieldsarray). - CategoryID: The folder ID where the DE will be stored.
- IsSendable:
truemeans the DE is sendable (you can send emails using this DE). - SendableDataExtensionField and SendableSubscriberField: These fields define how the sendable relationship is set up, using the “SubscriberKey” field.
Result Handling:
api.createItem("DataExtension", config)makes the SOAP API call to create the DE using the WSProxy method.- If the creation is successful (
result.Status == "OK"), it writes a success message (Data Extension created: [name]). - If there’s an error, it writes a failure message and could log the error for further inspection.
Error Handling: The entire operation is wrapped in a try-catch block to handle any exceptions. If something goes wrong (e.g., an invalid configuration), the error is caught and displayed using Write(Stringify(e)).
How to find folder id in marketing cloud engagement?
You may be wondering how to find the folder ID. There’s no ID visible in the UI interface, and to find one, you need to either hover over the folder to see the ID in the link, or inspect the page and check the HTML element. It couldn’t be more confusing, but you need to look for the “categoryId.”
Hover over folder
This works in Email Studio, and when you hover over the folder, the category ID will be displayed where the URL shows in the browser.
Inspect element to find folder id
This works in both Email Studio and Contact Builder, but Contact Builder is less hassle. Email Studio captures right-click to display an additional menu for the data extension navigation tree, making it a bit more cumbersome.
Right-click on any folder and select Inspect from the menu to open the inspector tool. The folder ID should be visible right away, next to the label, as an attribute called id on the span element.
