[SalesForce] Marketing Cloud – why is this SSJS failing to insert a data extension

Basically I am trying to write some SSJS that can clone the structure of DEs which is necessary in my role as a consultant and when I'm attempting to pull data out of a synchronized DE.

I've got the SSJS below. I'm currently trying to debug it in a landing page.

I can get all the way to where I set deObj – the JSON's properly formatted that's returned in debug – but when I try to actually add the data extension, I get the message "Error adding data extension."

Where am I going wrong?

Edit: I've made a change to the for loop to account for missing IsRequired per Gortonington's comment. Still getting the "Error adding data extension" issue though.

  <script runat="server">
Platform.Load("Core", "1.1.1");    
    try {      
      var Contact_Salesforce = DataExtension.Init("Contact_Salesforce");
      var jsonArray = Contact_Salesforce.Fields.Retrieve();
      for(var i = 0; i < jsonArray.length; i++) {
          delete jsonArray[i]['ObjectID'];
          jsonArray[i]['IsRequired'] = false;
          if(jsonArray[i]['IsPrimaryKey'] == true)
              jsonArray[i]['IsRequired'] = true;
      };
      var deObj = {"CustomerKey" : "SyncedContactsSalesforce2","Name" : "SyncedContactsSalesforce2","Fields" : jsonArray};
                   // Write(Stringify(deObj));
      var newDE = DataExtension.Add(deObj);
    } catch (ex) {
        Write("An error has occurred: " + Stringify(ex));
        Write("   deObj: " + Stringify(deObj));
        Variable.SetValue("@Result", Stringify(ex));
    }
</script>

Output looks like this:

{"CustomerKey":"SyncedContactsSalesforce2","Name":"SyncedContactsSalesforce2","Fields":
[{"Name":"_ContactKey","IsRequired":false,"FieldType":"Text","IsPrimaryKey":false,"MaxLength":254,"Ordinal":1,"DefaultValue":""}
...

Best Answer

So I've figured it out.

The synced DEs for Lead and Contact have a field called "_ContactKey". MC doesn't like it when a user attempts to add a field with an underscore at the beginning.

I also noticed that Fields.Retrieve doesn't pull back the right info for decimal fields (they get created as "18,undefined"), so for right now I am just converting them to text fields. If anyone has better advice on how to handle that let me know.

Also, I'm a JS newbie so there's probably more efficient ways to write this code, but this worked for me.

Final code for landing page:

%%[ 
    VAR @Result
]%%
<script runat="server">
Platform.Load("Core", "1.1.1");    
    try {      
      var DEtoCopy = DataExtension.Init("Contact_Salesforce");
      var jsonArray = DEtoCopy.Fields.Retrieve();

      for(var i = 0; i < jsonArray.length; i++) {
          delete jsonArray[i]['ObjectID'];
          jsonArray[i]['IsRequired'] = false;
          if(jsonArray[i]['IsPrimaryKey'] == true)
              jsonArray[i]['IsRequired'] = true;
          if(jsonArray[i]['Name'] == '_ContactKey')
              jsonArray[i]['Name'] = 'ContactKey';
          if(jsonArray[i]['FieldType'] == 'Decimal') {
             jsonArray[i]['FieldType'] = 'Text';
             jsonArray[i]['MaxLength'] = 50;
          };
      };
      var deObj = {"CustomerKey" : "SyncedContactsSalesforce","Name" : "SyncedContactsSalesforce","Fields" : jsonArray};
                   Write(Stringify(deObj));
      var newDE = DataExtension.Add(deObj);
    } catch (ex) {
        Write("An error has occurred: " + Stringify(ex));
        Write("   deObj: " + Stringify(deObj));
        Variable.SetValue("@Result", Stringify(ex));
    }
</script>
Related Topic