2

Is there a way to copy a data extension, either using ampscript or SSJS, to create a new data extension? Some of our work involves duplicating several data extensions with similar schema and it would be helpful to be able to do this programatically.

I've started with a landing page with a form where people enter the name of the DE they want to copy, and a new name for the new one. That is as far as I've got. I'm passing these 2 values through to the next page on submission, but don't really know how to proceed from here.

I have previously written ampscript to create a DE using a specific schema, but am unsure how to copy the entirety of an existing one.

Please note I want structure only, not data too.

5

EDIT

So I figured out that the object we need is the DataExtensionField object - which is likely where the issue was coming in. See below for a working version that has successfully copied a DE:

<script runat="server">

/******* Retrieves Fields *******/

var prox2 = new Script.Util.WSProxy();
var cols2 = ["Client.ID","CustomerKey","DefaultValue","FieldType","IsPrimaryKey","IsRequired","MaxLength","ModifiedDate","Name","ObjectID","Ordinal","Scale"];
var filter2 = {
    Property: "DataExtension.CustomerKey",
    SimpleOperator: "equals",
    Value: "AMPScriptTest"
};
var fields = prox2.retrieve("DataExtensionField", cols2, filter2); /* WSProxy Retrieve */

/* Results of the Retreive */
var fieldsResults = fields.Results;

var i;
var fieldsFinal = [] 

/*******  For loop to weed out some of the troublesome props in Results Object  ******/

for (i=0;i<fieldsResults.length;i++) {

        var fieldsRow = fields.Results[i];

        delete fieldsRow.ObjectID;
        delete fieldsRow.StorageType;

        if (fieldsRow.FieldType != "Text") {
             delete fieldsRow.MaxLength;
        }

        delete fieldsRow.DataExtension;
        delete fieldsRow.PartnerMap;
        delete fieldsRow.AttributeMaps;
        delete fieldsRow.Markups;
        delete fieldsRow.Precision;
        delete fieldsRow.Scale;
        delete fieldsRow.PicklistItems;
        delete fieldsRow.References;
        delete fieldsRow.Client;
        delete fieldsRow.PartnerKey;
        delete fieldsRow.PartnerProperties;
        delete fieldsRow.CreatedDate;
        delete fieldsRow.ModifiedDate;
        delete fieldsRow.ID;

    fieldsFinal.push(fieldsRow);
} 




/* Create DE */
var proxCreate = new Script.Util.WSProxy();

var customerKey = Platform.Function.GUID(); /* Replace with CustomerKey */
var name = "myDE"; /* Replace with your Name */
var description = "This is a test DE made in WSProxy" /* Replace with your Description */
var folder = 12345 /* This is the ID of the folder you want your DE placed in */

var de = {
    Name: name,
    CustomerKey: cusstomerKey,
    Description: description,
    Fields: fieldsFinal,
    CategoryID: folder
}

var create = proxCreate.createItem("DataExtension", de);

</script>

Please note I had to delete some of the properties in the field objects in order to push it through. Feel free to fiddle around with this to optimize it as best you can. I was limited on time so I didn't spend long on it.


There is this capability inside the SDK, otherwise the best method I found was utilizing the SOAP API.

SSJS has some functions that deceive you into thinking it is possible, but the DataExtension.Retrieve() function results come in a completely different format than what is needed in DataExtension.Add()

My recommendation would be to use WSProxy() Retrieve then parse those results and utilize a WSProxy() Create to build the duplicate DE.

Something like below:

Retrieve

var prox = new Script.Util.WSProxy();
var cols = ["Name","CustomerKey","CategoryID","IsSendable"];
var filter = {
    Property: "CustomerKey",
    SimpleOperator: "equals",
    Value: "ArtistsDE"
};
var desc = prox.retrieve("DataExtension", cols, filter);

Create

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

var guid = Platform.Function.GUID();
var name = "my test de - " + guid;

var de = {
    Name: name,
    CustomerKey: guid,
    Description: "Another DE added via SSJS",
    Fields: [{
        FieldType: "Text",
        Name: "ID",
        MaxLength: 36,
        IsPrimaryKey: true,
        IsNillable: false,
        IsRequired: true
    },
    {
        FieldType: "Text",
        Name: "Name",
        MaxLength: 200
    }],
    CategoryID: 101377
}

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

Another possible solution is to create a DE template (believe you need support to assist) and just create utilizing this template to duplicate DE set up.

  • Thanks - I've got the retrieve working but can't figure out how to parse the Fields from it into the Create block - in your example it looks like you're adding the fields in manually, whereas I want to pull a list of them in. – TCassa Sep 25 '18 at 15:58
  • you should be able to assign the results to variables and insert it into the create object. You can get the info out of the object and array similar to regular javascript. – Gortonington Sep 25 '18 at 16:30
  • 1
    @TCassa - I will try to find some time in next couple days to get more details for you and provide a more complete solution. – Gortonington Sep 26 '18 at 13:46
  • 1
    A workaround (off the top of my head) to this issue is to use the DataExtension retrieve I had in my original answer using Name as the filter - you then collect the CustomerKey from that retrieve and use it inside the DEFields call. I am sure there is a better solution out there, but this should meet your needs for now – Gortonington Oct 3 '18 at 13:03
  • 1
    var CustKey = Platform.Function.Stringify(desc.Results[0].CustomerKey); worked for me from the var desc = prox.retrieve("DataExtension", cols, filter); Retrieve call with a filter on Name – Gortonington Oct 4 '18 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.