How to create a Data Extension with server-side Javascript

How to create a Data Extension with server-side Javascript

Server-side Javascript (SSJS) is a powerful tool in Marketing Cloud. We can use it to our advantage and create Data Extensions on the fly.

Not a fan of reading? Jump to the code snippet.

Create a Data Extension

Creating a new Data Extension is easy enough and is pretty self-explanatory.

The following code will create a Data Extension named MyDataExtension or will throw an error if this Data Extension already exists.

<script runat="server">

    Platform.Load("core","1.1");

    var DE = "MyDataExtension"

    try {
        var obj = {
            "CustomerKey" : Platform.Function.GUID(),
            "Name" : DE,
            "Fields" : [
                { "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true },
                { "Name" : "MyData", "FieldType" : "Text", "MaxLength" : 50 },
                { "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : true }
            ]
        };
        DataExtension.Add(obj);
        Write("(+) Data Extension was created successfully." + "<br>");
    } catch (err) {
        Write("(!) Data Extension was not created. Error message: " + err + "<br>")
    }     

</script>

You can execute this code in the Preview mode and thanks to the Write() method you will be able to monitor everything that is happening.

Please note that the Data Extension will be created at the root folder level.

To specify every property of the Data Extension field you wish to create, please use the reference below (source):

Field attributes:

NameString
DefaultValueString
MaxLengthInt32 datatype
IsRequiredBoolean
IsPrimaryKey Boolean
FieldType List of values

FieldType values:

  • Text
  • Number
  • Date
  • Boolean
  • EmailAddress
  • Phone
  • Decimal
  • Locale

Keep in mind that not every field attribute is applicable to every field type.

For example, you can’t apply MaxLenght to a Boolean field type.

Move to a folder

In order to move the Data Extension to a new folder, we can simply get the Category ID of the folder with a specific name and add a new property in the object that we use for adding the Data Extension.

<script runat="server">

    Platform.Load("core","1.1");

    var DE = "MyDataExtension",
        FolderName = "General Folder";

    var FolderAttributes = Folder.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: FolderName });
    var FolderID = FolderAttributes[0].ID;     

    try {
        var obj = {
            "CustomerKey" : Platform.Function.GUID(),
            "Name" : DE,
            "CategoryID": FolderID,
            "Fields" : [
                { "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true },
                { "Name" : "MyData", "FieldType" : "Text", "MaxLength" : 50 },
                { "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : true }
            ]
        };
        var Status = DataExtension.Add(obj);
        If (Status == 'OK') {
            Write("(+) Data Extension was created successfully." + "<br>");
        } else {
            Write("(!) Data Extension was not created. Error message: " + Status + "<br>")
        }
    } catch (err) {
        Write("(!) Data Extension was not created. Error message: " + err + "<br>")
    }     

</script>

But what happens if the folder doesn’t exist? Jump to the following article to learn more.


Have I missed anything?

Please poke me with a sharp comment below or use the contact form.

Pay me a coffee

Want to say thanks? Pay me a coffee! Remember, I turn coffee into code.

  1. Hi Buddy thanks for this wonderfull article . I have a small issue regarding creating Data externsion

    Can u please help, using ur code i am able to generate DE but when i need to add dynamic de fields that is not working Following is the code

    Platform.Load(“core”,”1.1.1″);
    var birthdayDE = DataExtension.Init(“80c489be-20f3-448b-b683-2b098cae20dc”);
    var fields = birthdayDE.Fields.Retrieve();
    var DE = “MyDataExtensionss”,
    FolderName = “Amit”;
    console.log(fields);
    var FolderAttributes = Folder.Retrieve({ Property: “Name”, SimpleOperator: “equals”, Value: FolderName });
    var FolderID = FolderAttributes[0].ID;

    var obj = {
    “CustomerKey” : Platform.Function.GUID(),
    “Name” : DE,
    “CategoryID”: FolderID,
    “Fields” :fields
    };
    var Status = DataExtension.Add(obj);

  2. Hi Karna,

    I can already see that you are trying to use console.log which will break your code because it’s client-side Javascript.

    Please use Write(Stringify(fields)) or pass the variable to AMPscript and render it in server-side JS as follows:

    <script runat="server">
    Platform.Load('core','1.1.1');
    var birthdayDE = DataExtension.Init('80c489be-20f3-448b-b683-2b098cae20dc');
    var fields = birthdayDE.Fields.Retrieve();
    Platform.Variable.SetValue('@Fields', Stringify(fields));
    <script>
    <script runat="client">
    console.log('%%=v(@Fields)=%%')
    </script>

    Then, make sure that the Data Extension you are trying to create does not already exist with the same name.

    Tell me if it helped 🙂

  3. Hi Ivan thanks for the reply but i guess my query stll left, Actually i forgot to remove the console while posting. Just ignore it as i had removed it . My query is that i am not able to form new DE using the Fields i retrieve , IF i hardcode the fields i am able to form the DE but when i assign Fields=fields, i guess this is not working out

    Platform.Load(“core”,”1.1.1″);
    var birthdayDE = DataExtension.Init(“80c489be-20f3-448b-b683-2b098cae20dc”);
    var fields = birthdayDE.Fields.Retrieve();
    var DE = “MyDataExtensionss”,
    FolderName = “Amit”;

    var FolderAttributes = Folder.Retrieve({ Property: “Name”, SimpleOperator: “equals”, Value: FolderName });
    var FolderID = FolderAttributes[0].ID;

    var obj = {
    “CustomerKey” : Platform.Function.GUID(),
    “Name” : DE,
    “CategoryID”: FolderID,
    “Fields” :fields
    };
    var Status = DataExtension.Add(obj);

  4. Karna,

    When I tried to do the same I got the message: – “Primary key cannot be nullable”.

    Therefore, you need to add IsRequired = true to your primary key field.

    Please add the following:

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

    Let me know if it worked.

  5. You could also try this method, it gives better error handling:

    Platform.Load("core", "1.1");
    
    var prox = new Script.Util.WSProxy();
    var birthdayDE = DataExtension.Init('80c489be-20f3-448b-b683-2b098cae20dc');
    var fields = birthdayDE.Fields.Retrieve();
    
    for(var i = 0; i < fields.length; i++) {
        if(fields[i]['IsPrimaryKey'] == true ) fields[i]['IsRequired'] = true;
    }
    
    var FolderAttributes = Folder.Retrieve({ Property: 'Name', SimpleOperator: 'equals', Value: 'Amit' });
    var FolderID = FolderAttributes[0].ID;
    
    var obj = {
        'CustomerKey' : Platform.Function.GUID(),
        'Name' : 'MyDataExtensionss',
        'CategoryID': FolderID,
        'Fields' : fields
    }
    
    try {
        var response = prox.createItem("DataExtension", obj);
        Write(response.Results[0].StatusCode + ": " + response.Results[0].StatusMessage);
    } catch (err) {
        Write(Stringify(err));
    }
    
  6. HI ivan sorry to bother u but i am new to sfmc to just want to know where can we see the error part or console part . As of now i am working in automation script activity where i cant see these. Means can u please provide other way around on how to run this script

  7. Karna,

    In the Automation Studio you have the Activities tab. You have to run your automation and there you should see if your script errors.

    Hope this helped.

  8. Hi Ivan can you please helpme in this

    I am not able to upsert data using the following code.:I am getting parameters from the URL and using them to update the DE. Now the issue is either i can make excutioncontexttype= get or post not not both together , Can anybody help me out in this. Post is used to for updating

    Country:%%=v(@Country)=%%
    DE:%%=v(@DE)=%%
    Language:%%=v(@Language)=%%

    Platform.Load(“core”,”1.1.1″);
    var DE = Variable.GetValue(“@DE”);
    var ListOfContentDE = DataExtension.Init(DE);
    var fields = ListOfContentDE.Fields.Retrieve();
    var field= [];
    var fieldvalue=[];
    for(var i = 0; i < fields.length; i++) {
    field.push(fields[i].Name);
    fieldvalue.push(Request.GetFormField(fields[i].Name));
    }
    Write("” + field + “:”);
    var rows = Platform.Function.InsertDE(“ContentDE2”,field,fieldvalue);
    Write(“Rows:”); Write(“”);
    Write(“”+ rows + “”);

  9. Hi Karna. There are multiple places where your code could fail. To be able to debug more efficiently, please use the following function: try { [put your code here] } catch(err) { Write(Stringify(err)); }

  10. Thanks for this Ivan. I am a MC user in one of a Business Unit. You wrote: “Please note that the Data Extension will be created at the root folder level.” How can I run this code using a Script activity and create DE in the BU of the Activity. I believe the root is the top level BU (Global) and I am user in one of the child BU. Also I don’t have access to any other console apart from the Script Activity in Automation Studio. Thanks!

  11. Hi Carlito. The root folder is the Contact Builder root folder for the Data Extensions of the BU where you are running the script on.

    So if you are running the script on the parent BU to which you don’t have access, it’s going to be a problem.

    My suggestion is to get the external key of a specific folder of your BU and pass it as the CategoryId parameter to your script.

  12. Thanks. I ran the script using Automation Studio Script Activity on the BU that I have access to. I ran it once from the Activity. I thought the DE would appear within the Data Extensions folder of the same BU. Unfortunatelly, there is no log console to see any feedback. I will try with your tip to pass the CategoryId of a new test folder.

  13. Try to execute the script from a cloud page or an email. There you will see every error that occurs.

  14. Hi Ivan sorry to poke u again . I am having a small issue while creating a new DE from the existing one. The order of fields get changed in the new DE as compared to existing de which cause time trouble. Can u help me in this so that the order in which the Fields are same in both

  15. Karna,

    Each field has a hidden attribute called “Ordinal” which defines the order of fields.


    "Fields" : [
    { "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true , "Ordinal" : 1},
    { "Name" : "MyData", "FieldType" : "Text", "MaxLength" : 50, "Ordinal" : 2 },
    { "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : true, "Ordinal" : 3 }
    ]

  16. Hi Evan i was able to replicate the code to create DE from the existing one with the order fields as orignal, but a new issue has arrived. I am not ablt to add that record manually, only import activity is working for addding the record.

  17. Karna,

    I’m not sure to understand what you want to do. To add records manually, you need to go into the data extension click on the Records tab and click on add record button.

  18. Hi Ivan
    I am not able to add the record manually , it is throwing error to contact sfmc support when i am saving the record but when i use ssjs to insert record in that de it is working fine.

  19. actually i have got the point frmo where the issue is arising. I have a default column having “Boolean” type and default value set to “false” in main DE from which i am creating the new one . Now if i remove this column in the DE created from the ssjs, then i am able to store the record manually ,otherwise it throws me an error.
    Following the json of m y fields

    [{“Name”:”Country”,”ObjectID”:”6927e846-2355-428e-95d0-c9a86a1a4fec”,”FieldType”:”Text”,”IsPrimaryKey”:true,”MaxLength”:50,”Ordinal”:0,”DefaultValue”:””,”StorageType”:”Plain”,”IsRequired”:true},
    {“Name”:”Locale”,”ObjectID”:”587a5f80-a379-40bc-a248-ff593cc7c4df”,”FieldType”:”Text”,”IsPrimaryKey”:true,”MaxLength”:50,”Ordinal”:1,”DefaultValue”:””,”StorageType”:”Plain”,”IsRequired”:true},
    {“Name”:”EmailName”,”ObjectID”:”0886b10d-4a00-4a43-b799-2707c1df01c7″,”FieldType”:”Text”,”IsPrimaryKey”:true,”MaxLength”:50,”Ordinal”:2,”DefaultValue”:””,”StorageType”:”Plain”,”IsRequired”:true},
    {“Name”:”Cashier”,”ObjectID”:”afe3956d-cc5b-4124-88d4-5a9a3eabefb2″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:35,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Default”,”ObjectID”:”cd33a99e-0228-46e3-bcea-02f05f526804″,”FieldType”:”Boolean”,”IsPrimaryKey”:false,”MaxLength”:0,”Ordinal”:36,”DefaultValue”:”False”,”StorageType”:”Plain”},
    {“Name”:”Reg”,”ObjectID”:”689148bb-79eb-439b-a10e-5a1ed3101345″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:45,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Trans”,”ObjectID”:”67315568-2f14-458c-a415-3ab715654890″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:46,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Date”,”ObjectID”:”a46979b0-fc04-4aba-a41d-1e961cf4412f”,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:47,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”ReceiptExpires”,”ObjectID”:”6f2e72a8-d57d-41e5-ae27-c774314c9b8c”,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:48,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”BodyText”,”ObjectID”:”3f61378c-b989-4136-aed0-c4735b253ae1″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:49,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Title”,”ObjectID”:”aafa8672-16cb-47a1-85c4-c04f2fa8eea5″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:50,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”TitleMob”,”ObjectID”:”f3fcbc50-59a9-4384-8848-2adf96ea40a1″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:51,”DefaultValue”:””,”StorageType”:”Plain”}]

    DO u have any solution to this boolean value issue

  20. Hi Ivan
    Thanks for the reply but i was able to rectify the issue . Actually we are setting the length of Boolean object which is incorrect. To Rectify that i removed the length attribute for Boolean values and it worked for me

  21. @carlito Did it worked for you? I am running into the same issue as yours. Let me know if you did something which worked. Waiting for your reply 🙂

  22. @Ivan I tried using your above code wherein I tried to create a DE within SFMC using cloudpage and it worked on page load. But when I try to put the script under a function and call it onclick event for a button it does nothing. is there anything I’m doing wrong?

    Kindly ignore any typos..

    submit

    Platform.Load(“core”,”1.1″);
    function myFunction(){
    var DE = “MyNewDataExtensiGT”

    try {
    var obj = {
    “CustomerKey” : Platform.Function.GUID(),
    “Name” : DE,
    “Fields” : [
    { “Name” : “Id”, “FieldType” : “Number”, “IsPrimaryKey” : true, “IsRequired” : true },
    { “Name” : “MyData”, “FieldType” : “Text”, “MaxLength” : 50 },
    { “Name” : “Active”, “FieldType” : “Boolean”, “DefaultValue” : true }
    ]
    };
    DataExtension.Add(obj);

    Write(“(+) Data Extension was created successfully.” + “”);
    } catch (err) {
    Write(“(!) Data Extension was not created. Error message: ” + err + “”)

    }

    }

Leave a Reply

Your email address will not be published. Required fields are marked *

server-side Javascript
Up Next:

How to read data from a Data Extension with server-side Javascript

How to read data from a Data Extension with server-side Javascript