Skip to content

Instantly share code, notes, and snippets.

@wvpv
Last active August 24, 2023 15:30
Show Gist options
  • Save wvpv/02aeb043e167215f64a9ff5aefd02370 to your computer and use it in GitHub Desktop.
Save wvpv/02aeb043e167215f64a9ff5aefd02370 to your computer and use it in GitHub Desktop.
Mirror ImportResultsSummary Data to a Data Extension
<script runat="server" type="JavaScript">
Platform.Load("core", "1");
try {
var debug = false;
var prox = new Script.Util.WSProxy();
var DEKey = "ImportResultsSummary";
var DECategoryID = 12345; // Target Folder
// CREATE DATA EXTENSION
var result = null;
try {
result = prox.createItem("DataExtension",{"CustomerKey":DEKey,"Name":DEKey,"CategoryID":DECategoryID,"Fields":[
{"CustomerKey":"ClientID","Name":"ClientID","FieldType":"Number","IsPrimaryKey":true,"IsRequired":true},
{"CustomerKey":"ObjectID","Name":"ObjectID","FieldType":"Text","MaxLength":"100","IsPrimaryKey":true,"IsRequired":true},
{"CustomerKey":"StartDate","Name":"StartDate","FieldType":"Date","IsPrimaryKey":true,"IsRequired":true},
{"CustomerKey":"EndDate","Name":"EndDate","FieldType":"Date","IsPrimaryKey":true,"IsRequired":true},
{"CustomerKey":"TaskResultID","Name":"TaskResultID","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"ImportDefinitionCustomerKey","Name":"ImportDefinitionCustomerKey","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"ImportType","Name":"ImportType","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"ImportStatus","Name":"ImportStatus","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"ID","Name":"ID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"DestinationID","Name":"DestinationID","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"TargetDataExtensionFolderPath","Name":"TargetDataExtensionFolderPath","FieldType":"Text","MaxLength":"1000","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"TargetDataExtensionName","Name":"TargetDataExtensionName","FieldType":"Text","MaxLength":"254","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"TotalRows","Name":"TotalRows","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"NumberDuplicated","Name":"NumberDuplicated","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"NumberErrors","Name":"NumberErrors","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"NumberSuccessful","Name":"NumberSuccessful","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"NumberRestricted","Name":"NumberRestricted","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
]});
if (debug) {
//Write("<br>result:"+ Stringify(result))
}
} catch (e0) {
if (debug) {
Write("<hr>e0: " + Stringify(e0))
}
}
// RETREIVE THE OBJECT
var objCols = ["Client.ID","ObjectID","StartDate","EndDate","TaskResultID","ImportDefinitionCustomerKey","ImportType","ImportStatus","ID","DestinationID","TotalRows","NumberDuplicated","NumberErrors","NumberSuccessful","NumberRestricted"];
//var filter = {Property:"ImportDefinitionCustomerKey", SimpleOperator:"notEquals", Value: "whee"};
var filter = null;
var obj = prox.retrieve(DEKey, objCols, filter);
if (debug) {
Write("<br>obj: " + Stringify(obj));
Write("<hr>obj.Results.length: " + obj.Results.length);
}
if (obj.Status == "OK") {
var results = obj.Results;
if (results && results.length > 0) {
for (i = 0; i < results.length; i++) {
var result = results[i];
var ClientID = result.Client.ID;
var ObjectID = result.ObjectID;
var StartDate = result.StartDate;
var EndDate = result.EndDate;
var TaskResultID = result.TaskResultID;
var ImportDefinitionCustomerKey = result.ImportDefinitionCustomerKey;
var ImportType = result.ImportType;
var ImportStatus = result.ImportStatus;
var ID = result.ID;
var DestinationID = result.DestinationID;
var TotalRows = result.TotalRows;
var NumberDuplicated = result.NumberDuplicated;
var NumberErrors = result.NumberErrors;
var NumberSuccessful = result.NumberSuccessful;
var NumberRestricted = result.NumberRestricted;
var deCols = ["ObjectID","Name","CategoryID"];
var deFilter = {
Property: "ObjectID",
SimpleOperator: "equals",
Value: DestinationID
};
var TargetDataExtensionName = ""
var TargetDataExtensionFolderPath = ""
var deObj = prox.retrieve("DataExtension", deCols, deFilter);
if (debug) {
Write("<br>deObj.length: " + deObj.length);
}
if (deObj.length > 0) {
TargetDataExtensionFolderPath = getFolderPath(deObj.Results[0].CategoryID);
TargetDataExtensionName = deObj.Results[0].Name;
}
var keys = ["ClientID", "ObjectID", "StartDate", "EndDate"];
var keyVals = [ClientID, ObjectID, StartDate, EndDate];
var cols = ["TaskResultID","ImportDefinitionCustomerKey","ImportType","ImportStatus","ID","DestinationID","TotalRows","NumberDuplicated","NumberErrors","NumberSuccessful","NumberRestricted","TargetDataExtensionFolderPath","TargetDataExtensionName"];
var colVals = [TaskResultID, ImportDefinitionCustomerKey, ImportType, ImportStatus, ID, DestinationID, TotalRows, NumberDuplicated, NumberErrors, NumberSuccessful, NumberRestricted,TargetDataExtensionFolderPath,TargetDataExtensionName];
var rowsUpserted = Platform.Function.UpsertData(DEKey, keys, keyVals, cols, colVals);
if (debug) {
Write("<br>rowsUpserted: " + rowsUpserted);
}
} // end for-loop
} // end results check
} // end status if-then
} catch (e) {
Write("<hr>e: " + Stringify(e));
}
function getFolderPath(categoryID) {
var list = "";
var path = function(id) {
if (id > 0) {
var cols = ["ID","ParentFolder.ID","Name"];
var filter = {
Property: "ID",
SimpleOperator: "equals",
Value: id
};
var opts = { BatchSize: 1500 };
var props = { QueryAllAccounts: true };
var data = prox.retrieve("DataFolder", cols, filter, opts, props);
if (data && data.Results) {
list = data.Results[0].Name + " > " + list;
return path(data.Results[0].ParentFolder.ID);
} else {
return id;
}
} else {
return id;
}
};
path(categoryID);
return list;
}
</script>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment