0

For a set of specific Business units, I am trying to retrieve Data extensions older than 92 days (using ModifiedDate) and load them into another DE. And, I would like to retrieve these fields: DE Name, DE Description, folder Name, isSendable, CreatedDate, ModifiedDate, MID, DEs CustomerKey. My script is retrieving all the fields, but not the "Folder Name" except Parent BU's DEs folder names are showing up (which is where I'm running this script). Can someone please help?

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

try { 

var prox = new Script.Util.WSProxy();      // Sets the Proxy - DO NOT CHANGE
var custKey = "DE_inventory_umf_arch";      // External Key of the DE that will hold all the data we gather
var queryAllAccounts = true;               // Sets BU to ALL business units
var MidBuDE = "BusinessUnit";

  var deArr = getAllDeInfo();      //returns all the DE info from this Business Unit 

  for (var a=0; a<deArr.length;a++) {    //Iterate through the array holding each of the objects from the DE info return
    var deData = deArr[a].Results;       //Set the Object for us to pull

    for (var e=0; e< deData.length; e++) { //iterate through the Results of that object, setting each value
      var name = deData[e].Name;
      var buID = deData[e].Client.ID;
      
    var dt = new Date();
    var date_end = addDate(dt, -92, 'days');
    var mod_date = deData[e].ModifiedDate;

      if (mod_date < date_end) {  
        var customerkey = deData[e].CustomerKey
        var desc = deData[e].Description
        var catID = deData[e].CategoryID
        var isSendable = deData[e].IsSendable
        var v_CreatedDate = deData[e].CreatedDate
        var v_ModifiedDate = deData[e].ModifiedDate
       
       //filter for DE Folder
       var filter = {
                        Property: "ID",
                        SimpleOperator: "equals", 
                        Value: catID
            }

        var req = prox.retrieve("DataFolder", ["Name"], filter);
        var folderPath = req.Results[0].Name;   
      
        var nameArr = ["Description","FolderPath","IsSendable","CreatedDate","ModifiedDate"] 
            // Array of Non Primary Key names to pass to Data Extension as Column Names

        var valueArr = [desc,folderPath,isSendable,v_CreatedDate,v_ModifiedDate] 
            //Array of Non Primary Key values to pass to Data Extension as Column Values

        var upsertDeInfo = Platform.Function.UpsertData(custKey, ["MID","Name", "CustomerKey"], [buID, name, customerkey], nameArr, valueArr)
            //Upserts the pulled value into the data extension, using the above primary keys
      }  
    }  
  }  

//DE Retrieve fx()
function getAllDeInfo() {

  var cols = ["ObjectID","CustomerKey","Name","CategoryID","CreatedDate","ModifiedDate","Client.ID","Description","IsSendable"];
  
//Query Affiliated BUs 
  var BuDE = DataExtension.Init(MidBuDE);
  var buMID;
  var buMID_Arr = [];
  var data = BuDE.Rows.Retrieve();
  var end = data.length;
  for (var i = 0; i < end; i++) {       //loop through BUsinessUNit rows --140-ish 
      var buMID = data[i]['MID'];
      buMID_Arr.push(buMID)
  }
    //Include Affiliated BUs 
    var filter = {Property: "Client.ID"
                  , SimpleOperator: "IN"
                  , Value: buMID_Arr
                 };
  
    var reqID = null
    var moreData = true;
    var desc = [];

    while(moreData) {
      moreData = false;
      var data = reqID == null ?
      prox.retrieve("DataExtension", cols, filter, queryAllAccounts): prox.getNextBatch("DataExtension", reqID); //retrieve entire BUs DEs
      if(data != null) {
        moreData = data.HasMoreRows;
        reqID = data.RequestID;
        if(data && data.Results) {
          desc.push(data);
        }
      }
    }
 return desc;
  
}

// function to create date structure 
function addDate(dt, amount, dateType) {
  switch (dateType) {
    case 'days':
      return dt.setDate(dt.getDate() + amount) && dt;
    case 'weeks':
      return dt.setDate(dt.getDate() + (7 * amount)) && dt;
    case 'months':
      return dt.setMonth(dt.getMonth() + amount) && dt;
    case 'years':
      return dt.setFullYear( dt.getFullYear() + amount) && dt;
     }
   }
} 
catch(e) {  
 Write(Stringify(e))  

}
</script>

enter code here
4
  • Was able to figure out: by changing this var req = prox.retrieve("DataFolder", ["Name"], filter); Jun 5 at 17:15
  • to this by adding additional criteria: queryAllAccounts. Unfortunately, it timed out because we have over 150 BU Jun 5 at 17:19
  • var req = prox.retrieve("DataFolder", ["Name"], filter, queryAllAccounts); Jun 5 at 17:20
  • Interesting that it does not let flag answered Jun 5 at 17:21

1 Answer 1

-1

Ended up adding queryAllAccounts parameter

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.