2

I found some useful documentation/past examples of using wsproxy to copy and create a duplicate data extension.

Goal: I would like to copy a DE from a source business unit and then create a DE with the same structure in a target business unit under the same parent account.

Issue: The code works when it is contained within the same business (i.e. data to be copied and the target folder are all within the same BU. The issue occurs when I try to use pull from one business unit and create it in another. I tried adding the code (retrieveprox.setClientId({ "ID": xxx, "UserID": yyy});) before calling the retrieve and create scripts, but it returns a 500 internal error.

The documentation on "setClientId({ "ID": xxx, "UserID": yyy})" is very lacking, so maybe I am making a mistake here. How can I be sure I am using the right values for both ID and UserID?

Code:

<script runat="server">

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


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

retrieveprox.setClientId({ "ID": xxx, "UserID": yyy});

var cols2 = ["Client.ID","CustomerKey","DefaultValue","FieldType","IsPrimaryKey","IsRequired","MaxLength","ModifiedDate","Name","ObjectID","Ordinal","Scale"];
var filter2 = {
    Property: "DataExtension.CustomerKey",
    SimpleOperator: "equals",
    Value: "8ACA6905-30E3-45F1-BE16-287D45F8F057"
};

var fields = retrieveprox.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();


   proxcreate.setClientId({ "ID": xxxx, "UserID": yyyy});

var customerKey = "testDE_7"; 
var name = "testDE_7"; 
var description = "This is a test DE made in WSProxy" 
var folder = 761 

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

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

</script>
  • For some reason, the copy/paste of the code was not too successful, but the code works without the setClientId({ "ID": xxxx, "UserID": yyyy}); lines. So I am thinking the issue is there. – Lyle Mar 22 at 17:00
1

I have found that using ID (MID) combined with UserID can wind up causing issues. I tend to just use the ID section, like so: retrieveprox.setClientId({ "ID": mid });.

Removing UserID I was able to execute this code successfully.

See below for what I used:

<script runat="server">

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

var retrieveMid =  8675309;
var createMid =  8675309;
var folderID = 761;
var deCustKey = '8ACA6905-30E3-45F1-BE16-287D45F8F057';

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


retrieveprox.setClientId({ "ID": retrieveMid }); //Impersonates the BU

var cols2 = ["Client.ID","CustomerKey","DefaultValue","FieldType","IsPrimaryKey","IsRequired","MaxLength","ModifiedDate","Name","ObjectID","Ordinal","Scale"];
var filter2 = {
    Property: "DataExtension.CustomerKey",
    SimpleOperator: "equals",
    Value: deCustKey
};

var fields = retrieveprox.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();


   proxCreate.setClientId({ "ID": createMid }); //Impersonates the BU

var customerKey = "testDE_7"; 
var name = "testDE_7"; 
var description = "This is a test DE made in WSProxy" 
var folder = folderID 

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

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

</script>
  • Thank you for parameterizing the code! When slotting my own MIDs into the code, it works successfully when both the retrieveMid and createMid are the same (as they are in your example as well). However, once I switch the retrieveMid to the MID of another business unit, the new Data Extension does not appear in the specified folder of the createMid after using the cloud page. The page does not throw any errors though and it looks to have processed successfully, but for some reason it does not seem to be completing. Can you verify the DE gets created for you if the retrieveMid is different? – Lyle Mar 22 at 17:56
  • yes it worked for me. I would do two things - 1: Verify your permissions on your user to access other BUs. 2: Output the results of your proxy call (Write(Stringify(create))) and/or do a try/catch around it to get more details. – Gortonington Mar 22 at 17:59
  • I appreciate your confirmation, but I also double checked my permissions and as admin I have access to all BUs. I tried adding two try/catches around both the retrieve and create, but it does not write anything to the page, which has me bewildered. Added: try { var fields = retrieveprox.retrieve("DataExtensionField", cols2, filter2); /* WSProxy Retrieve */ } catch (e) { Write(Stringify(e)); } and try { var create = proxCreate.createItem("DataExtension", de); } catch (e) { Write(Stringify(e)); } but I did not have luck. – Lyle Mar 22 at 18:43
  • any information inside of a try/catch statement will not be accessible to any scripts outside of it, I would recommend doing it within a single try/catch. Also, I highly recommend outputting the var associated with the execution of your proxy calls as they should contain the results of the call. – Gortonington Mar 22 at 18:55
  • 1
    I was able to successfully troubleshoot with your addition of that code. The issue was not in the retrieve, but rather the create. The retrieved DE that I had selected did not have max lengths on some of the fields, so the create wsproxy was failing. Thanks again for your insight! – Lyle Mar 25 at 16:00

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.