0

We have a requirement to retrieve the list of data extension names or external key values under a folder in SFMC and store them in another (target) data extension.

I have tried this option mentioned in the post below but unfortunately it seems to get the values and display on a cloud page but not retrieve the values to a target data extension. How can I retrieve a list of Data Extensions in a folder using SSJS

Can someone provide an insight on how this can be achieved?

2

I wrote an article on a similar situation here. The main difference being my article was about an inventory of DE for your whole BU and included many different values and properties that may be unnecessary for you.

I would use this function:

function getAllDeInfo(mid) {
  var prox = new Script.Util.WSProxy();

  prox.setClientId({ "ID": mid }); //Impersonates the BU
  
  var filter = {
    Property: "CategoryID",
    SimpleOperator: "equals",
    Value: {{yourFolderID}}
};
  var cols = ["Name","CustomerKey","IsSendable"];
  var reqID = null
  var moreData = true;
  var desc = [];

  while(moreData) {
    moreData = false;
    var data = reqID == null ?
      prox.retrieve("DataExtension", cols, filter):
      prox.getNextBatch("DataExtension", reqID);

    if(data != null) {
      moreData = data.HasMoreRows;
      reqID = data.RequestID;
      if(data && data.Results) {
        desc.push(data);
      }
    }
  }

  return desc;
}

to collect the JSON with all the DEs inside that specific CategoryID (Folder).

You then just:

<script runat=server>
Platform.Load("Core","1.1.1");

//CustomerKey for Inventory DE
var custKey = 'DE_Inventory'

//get bulk return of Data Extension
var desc = getAllDeInfo(mid);

//verify a valid return
if (desc) {

 //iterate through DE return for individual results
  for(var e=0; e < desc.length; e++) {

    var deData = desc[e];
    //iterate through each object to collect the properties of the DE
    for(var i=0; i < deData.Results.length; i++) {
      var name                = deData.Results[i].Name
      var customerKey         = deData.Results[i].CustomerKey
      var deIsSendable        = deData.Results[i].IsSendable

      //Upserts the corresponding info into the Inventory DE
      var upsertDeInfo = Platform.Function.UpsertData(custKey, ["Name", "CustomerKey"], [name, customerKey], ["IsSendable"], [deIsSendable])

    }
  }
}
</script>

to upsert the info into your Data Extension.

1
  • This worked perfectly, thank you! Nov 11 '20 at 1:17
2

The post you mentioned above will return JSON which will contain all the DE Information. To store that DE Information, whichever field you would want to save in your Target DE, you need to create one. For Instance, I created a DE 'DEDetails', with the below configuration

enter image description here

A sample JSON from the 'retrieve' function would be something like this:

{
"Name": "testDE",
"Description": "",
"IsSendable": true,
"IsTestable": false,
"CategoryID": 9162,
"IsPlatformObject": false,
"CustomerKey": "75E3D454-ECFC-467E-9885-024656061B86",
"CreatedDate": "2019-12-11T07:01:40.047",
"ModifiedDate": "2019-12-12T06:41:04.190",
"ObjectID": "d4810259-161c-ea11-b833-b883035b8991",
"Client": {
    "ID": MIDofBu
},
"Status": "None",
"PartnerKey": "",
"SendableDataExtensionField": {
    "Name": "FieldName"
},
"SendableSubscriberField": {
    "Name": "_SubscriberKey"
}
    }

In addition to the existing code, you need to iterate through JSON and use UpsertData (Assuming you are writing the code on a cloud page/script activity in Automation Studio). Your final code will be like below:

    <script runat="server">

Platform.Load("core","1.1.1");
try
{
  
  var results = DataExtension.Retrieve({Property:"CategoryID",SimpleOperator:"equals",Value:"9162"});
  for (var i = 0; i < results.length; i++)
  {
    var Name = results[i].Name;
    var CustomerKey = results[i].CustomerKey;
    var IsSendable = results[i].IsSendable;    
   // Write("Name = "+ Name + "CustomerKey = "+ CustomerKey);
    var rows = Platform.Function.UpsertData("DEDetails",["Name", "ExternalKey"],[Name,CustomerKey],["IsSendable"],[IsSendable]);
  } 
 // Write(Stringify(results));
}

 catch(e){
Write(Stringify(e));
}

</script>

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