Last active
August 24, 2023 15:30
-
-
Save wvpv/02aeb043e167215f64a9ff5aefd02370 to your computer and use it in GitHub Desktop.
Mirror ImportResultsSummary Data to a Data Extension
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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