**EDIT** 2019-09-22: Added new function at bottom of article to allow for Sendable and Retention inside DE.


Copying a data extension inside the UI of SFMC is not too difficult and can be done via the Wizard. The issue comes in that this is a purely manual process and cannot be automated or programmatic.

There is a sample PHP solution in the documentation, but very little to no documentation on doing this via ‘raw’ SOAP API envelopes.

The solution to this is to utilize the API to create the data extension via the ‘DataExtension’ object. The problem there is that there is no ‘Copy’ Method in the SOAP objects. There is only Retrieve, Create, Delete, Perform and Update.

Another big issue is that although you can create a DE by adding in the fields, the ‘DataExtension’ object does not let you retrieve these fields.

Sample result of a Describe to ‘DataExtension’:

<Properties>
   <PartnerKey xsi:nil="true" />
   <ObjectID xsi:nil="true" />
   <Name>Fields</Name>
   <DataType>DataExtensionField[]</DataType>
   <IsUpdatable>true</IsUpdatable>
   <IsRetrievable>false</IsRetrievable>
</Properties>

This means that to get the fields associated with a DE, you need to also retrieve off the ‘DataExtensionField’ object.

Therefore to get a full picture of the DE in order to copy it, you are now required to make 2 different Retrieve API calls. Making the ability

So first, lets concentrate on gathering the necessary fields from the ‘DataExtension’ object. You start out with building a general WSProxy API call:

var prox = new Script.Util.WSProxy(); 

var cols = ["Name","CustomerKey","CategoryID","Description","IsSendable","SendableDataExtensionField.Name","SendableSubscriberField.Name","DataRetentionPeriodLength","DataRetentionPeriodUnitOfMeasure","RowBasedRetention","ResetRetentionPeriodOnImport","DeleteAtEndOfRetentionPeriod","RetainUntil","DataRetentionPeriod"];

var filter = {

    Property: "CustomerKey",

    SimpleOperator: "equals",

    Value: "yourDEKey"

};

var desc = prox.retrieve("DataExtension", cols, filter);

Now an important note is the ‘cols’ variable. This is where you will want to put an array of the columns you want to retrieve. For my sample I put a very comprehensive list of the columns we need for a thorough retrieve. Feel free to remove any that may be unnecessary for your call.

The next important variable is the filter, which will make sure your returned data only contains the information on the DE you are copying. I usually recommend retrieving off Customerkey (aka ExternalKey), but you can also filter off other fields if you feel it is more relevant.

Finally we perform the call via the desc variable which will now contain your DE information. I would then recommend putting all the above columns you retrieved into SSJS variables for easy reference later. Something like:

var isSendable = desc.Results[0].IsSendable;

We then move on to retrieving the fields for this Data Extension. You would create the basic WSProxy like before, but now aiming at ‘DataExtensionField’ Object.

  var proxy = new Script.Util.WSProxy();
  var cols2 = ["CustomerKey","Name","FieldType","IsPrimaryKey","MaxLength","Ordinal","DefaultValue","IsRequired"];
  var filter2 = {
      Property: "DataExtension.CustomerKey",
      SimpleOperator: "equals",
      Value: "yourDEKey"
  };
  var deFields = proxy.retrieve("DataExtensionField", cols2, filter2);
  var fieldLength = deFields.Results.length

Similar to the DE Retrieve, we have the columns, a filter and the perform variables. We also have now added a ‘fieldLength’ variable to help with the next part.

Next we will need to create a new, empty array and push our results from the deFields retrieve into it. The reason we cannot just use the results directly is that it includes many fields inside of it that are not updatable, so they need to be trimmed out of the array.

var deFieldArray = deFields.Results

var fieldArray = []

for (var a = 0; a < fieldLength; a++) {

  var fieldObj = deFieldArray[a]

//Fields that need to be removed prior to creation of new DE
  delete fieldObj.AttributeMaps;
  delete fieldObj.CustomerKey;
  delete fieldObj.ObjectID;
  if (fieldObj.MaxLength == "" || fieldObj.MaxLength == 0) {
    delete fieldObj.MaxLength;
  }
  delete fieldObj.StorageType;
  delete fieldObj.DataExtension;
  delete fieldObj.DataType;
  delete fieldObj.IsCreatable;
  delete fieldObj.IsUpdatable;
  delete fieldObj.IsRetrievable;
  delete fieldObj.IsQueryable;
  delete fieldObj.IsFilterable;
  delete fieldObj.IsPartnerProperty;
  delete fieldObj.IsAccountProperty;
  delete fieldObj.PartnerMap;
  delete fieldObj.Markups;
  delete fieldObj.Precision;
  delete fieldObj.Scale;
  delete fieldObj.Label;
  if (fieldObj.MinLength == "" || fieldObj.MinLength == 0) {
    delete fieldObj.MinLength;
  }
  delete fieldObj.CreatedDate;
  delete fieldObj.ModifiedDate;
  delete fieldObj.ID;
  delete fieldObj.IsRestrictedPicklist;
  delete fieldObj.PicklistItems;
  delete fieldObj.IsSendTime;
  delete fieldObj.DisplayOrder;
  delete fieldObj.References;
  delete fieldObj.RelationshipName;
  delete fieldObj.Status;
  delete fieldObj.IsContextSpecific;
  delete fieldObj.Client;
  delete fieldObj.PartnerProperties;

  fieldArray.push(fieldObj);

  var fieldObj = "";

}

First we create a variable to hold the Array of DE Field Objects (deFieldArray) and then another array to hold the final results (fieldArray). From there we build out the for loop to iterate through each object inside of the array to recreate each and every field inside the DE.

Inside of that for loop we then create ‘fieldObj’ to pull in that specific object from the array. You will then notice a long list of ‘deletes’. This removes the fields that would create an issue in our create due to not being updatable. It also has a couple conditionals to remove any other potential issues. For instance if MaxLength is empty, it will cause an error in the API call, so we delete it if empty.

Then after that long list of ‘deletes’ , we push the fieldObj that has been trimmed into our new array (fieldArray). Then we empty the fieldObj variable to ensure there is no carry over in the new for iteration.

We now have all the pertinent information from the DE as well as the fields for that DE. On to the final step – creating the new ‘copy’ DE.

This is by far the easiest step as it is a super simple WSProxy Create call:

var prox = new Script.Util.WSProxy();

var key = deKey + "_copy";
var name = deName + "_copy";

var de = {
    Name: name,
    CustomerKey: key,
    Description: deDescription,
    CategoryID: deCategoryId,
    IsSendable: deIsSendable,
    SendableDataExtensionField: [
        Name: desendableDatatExtensionFieldName
    ],
    ...,
    Fields: fieldArray
}

var res = prox.createItem("DataExtension", de);

For the sake of brevity, I left out some of the properties to add in (full list from ‘cols’ inside original retrieve. You then create the DE and retrieve the response via the ‘res’ variable.

And there you go, you have now copied a DE via the API using WSProxy!


***EDIT***

I made some changes to this to create a function to account for IsSendable and Retention settings.

Below is a quick function I created that should be able to copy any DE with just an input of the Source DE and the name of the Target DE. You can also optionally put in an MID for source and new MID for target – but this is dependent on your permissions as well.

//copy existing DE schema
function copyDE(sourceDE,targetDEName,mid, mid2) {

  //Retrieve DE information
  var retrieveProx = new Script.Util.WSProxy();

  if(mid) {
    // Set ClientID 
    retrieveProx.setClientId({ "ID": mid });
  }

  var cols = ["Name","CustomerKey","CategoryID","IsSendable","SendableDataExtensionField.Name", "SendableSubscriberField.Name", "DataRetentionPeriodLength", "DataRetentionPeriod", "DeleteAtEndOfRetentionPeriod", "RowBasedRetention", "ResetRetentionPeriodOnImport"];
  var filter = {
      Property: "CustomerKey",
      SimpleOperator: "equals",
      Value: sourceDE
  };
  var desc = retrieveProx.retrieve("DataExtension", cols, filter);
  
  var sendable = desc.Results[0].IsSendable;
  var retention = desc.Results[0].DataRetentionPeriodLength;

  if (retention && retention > 0) { 
    retention = true  
    Write('Retention: ' + retention + '<br><br>')
  }
  var DEcategoryID = desc.Results[0].CategoryID;

  if(sendable) {
    var sendableName = desc.Results[0].SendableDataExtensionField.Name;
    var RelatesOnSub = desc.Results[0].SendableSubscriberField.Name;
  }

  if (retention) {
    var retentionPeriodLength = desc.Results[0].DataRetentionPeriodLength;
    var retentionPeriod = desc.Results[0].DataRetentionPeriod;
    var deleteRetentionPeriod = desc.Results[0].DeleteAtEndOfRetentionPeriod;
    var rowRetention = desc.Results[0].RowBasedRetention;
    var resetRetention = desc.Results[0].ResetRetentionPeriodOnImport;
    var retentionPeriodUnit = desc.Results[0].DataRetentionPeriodUnitOfMeasure;
  }


  // Retrieve DE Field Schema
  var fieldProx = new Script.Util.WSProxy();

    if(mid) {
    // Set ClientID 
    fieldProx.setClientId({ "ID": mid });
  }

  var cols2 = ["CustomerKey","Name","FieldType","IsPrimaryKey","MaxLength","Ordinal","DefaultValue","IsRequired"];
  var filter2 = {
      Property: "DataExtension.CustomerKey",
      SimpleOperator: "equals",
      Value: sourceDE
  };
  var deFields = fieldProx.retrieve("DataExtensionField", cols2, filter2);

  var fieldLength = deFields.Results.length
  var deFieldObj = deFields.Results

  var fieldArray = []

  // Organize and format DE Field Schema
  for (var a = 0; a < deFieldObj.length; a++) {

    var fieldObj = deFields.Results[a]

  //Fields that need to be removed prior to creation of new DE
    delete fieldObj.AttributeMaps;
    delete fieldObj.CustomerKey;
    delete fieldObj.ObjectID;
    if (fieldObj.MaxLength == "" || fieldObj.MaxLength == 0) {
      delete fieldObj.MaxLength;
    }
    delete fieldObj.StorageType;
    delete fieldObj.DataExtension;
    delete fieldObj.DataType;
    delete fieldObj.IsCreatable;
    delete fieldObj.IsUpdatable;
    delete fieldObj.IsRetrievable;
    delete fieldObj.IsQueryable;
    delete fieldObj.IsFilterable;
    delete fieldObj.IsPartnerProperty;
    delete fieldObj.IsAccountProperty;
    delete fieldObj.PartnerMap;
    delete fieldObj.Markups;
    delete fieldObj.Precision;
    delete fieldObj.Scale;
    delete fieldObj.Label;
    if (fieldObj.MinLength == "" || fieldObj.MinLength == 0) {
      delete fieldObj.MinLength;
    }
    delete fieldObj.CreatedDate;
    delete fieldObj.ModifiedDate;
    delete fieldObj.ID;
    delete fieldObj.IsRestrictedPicklist;
    delete fieldObj.PicklistItems;
    delete fieldObj.IsSendTime;
    delete fieldObj.DisplayOrder;
    delete fieldObj.References;
    delete fieldObj.RelationshipName;
    delete fieldObj.Status;
    delete fieldObj.IsContextSpecific;
    delete fieldObj.Client;
    delete fieldObj.PartnerProperties;

    fieldArray.push(fieldObj);

    //set sendable field type
    if(sendableName = fieldObj.Name) {
      var sendableFieldType = fieldObj.FieldType
    }

   //Reset fieldObj
    var fieldObj = "";

  }


  // Create New DE
  var createProx = new Script.Util.WSProxy();

    if(mid2) {
    // Set ClientID 
    createProx.setClientId({ "ID": mid2 });
  } else if(mid) {
    // Set ClientID 
    createProx.setClientId({ "ID": mid });
  }

  var name = targetDEName;
  var de = {
  Name: name,
  CustomerKey: name,
  Description: "",
  Fields: fieldArray,
  CategoryID: DEcategoryID
  };

  if(sendable) {

    if (RelatesOnSub = '_SubscriberKey') { 
      RelatesOnSub = 'Subscriber Key' 
    }

    de.IsSendable = true
    de.SendableDataExtensionField = {"Name": sendableName, "FieldType": sendableFieldType }
    de.SendableSubscriberField = {"Name": RelatesOnSub}
    de.CategoryID = DEcategoryID

  }

  if (retention) {

    de.DataRetentionPeriodLength = retentionPeriodLength;
    de.DataRetentionPeriod = retentionPeriod;
    de.DeleteAtEndOfRetentionPeriod = deleteRetentionPeriod;
    de.RowBasedRetention = rowRetention;
    de.ResetRetentionPeriodOnImport = resetRetention;
    de.DataRetentionPeriodUnitOfMeasure = retentionPeriodUnit;
  }

  var res = createProx.createItem("DataExtension", de);

  return res;
}
Tags: , , , , , , ,
Subscribe
Notify of
guest
4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
trackback
WSProxy – Upsert Subscribers from a Data Extension | Gortonington
3 years ago

[…] I manually declared the fields to be retrieved in my example, but if you have dynamic fields inside your target DEs, you can utilize a WSProxy retrieve on the DataExtensionField object to collect the fields you want retrieved. You can find an example of that process here. […]

Murilo
Murilo
2 years ago

Hello, Gortonington, first of all, congratulations to share your knowledge with the world, I got a question, can I using this code, send records from a list to a data extension ? Just changing some parts of the code ?

Kelvid
Kelvid
1 year ago

Hi Gortonington,

Thanks for sharing the knowledge, appreciate it very much.
I found there is a typo for the following: sendableName = fieldObj.Name
I think the correct one should be: sendableName == fieldObj.Name

The intention is to compare the two variables and perform the next step.
Do correct me if I’m wrong.

Thomas Porter
Thomas Porter
11 months ago

I found that the script created Data Extension fields in random order.

By adding a sort by Ordinal property to the deFieldObj array, this can be corrected:

var deFieldObj = deFields.Results
// added here
deFieldObj.sort(function(a, b) {
return parseFloat(a.Ordinal) – parseFloat(b.Ordinal);
});
//end of addition
var fieldArray = []