Streamline Your Search: SSJS solution to Identify Query Activities Targeting a Specific Data Extension

Are you overseeing an SFMC account with numerous automations?

Identifying the query activities that populate a specific data extension can be quite challenging. However, here comes a custom solution.

Simply specify the target data extension (DE) and execute a script. It will reveal the relevant query activities and their parent automations, along with the location responsible for updating this target DE.

The following code snippets helps to identify all SQL query activities within an automation process that targets a specified Data Extension (DE):

Code Snippet for extracting Query Definition:

var filter = {

Property: "DataExtensionTarget.CustomerKey",

SimpleOperator: "equals",

Value: customerKey

};

var cols = ["Name", "CustomerKey", "CategoryID"];

var data = api.retrieve("QueryDefinition", cols, filter);

Here, our objective is to retrieve the query definitions that correspond to the specified Data Extension (DE), which serves as the target DE.

Code Snippet for find the parent automation for a query activity :

//--- get program ID using activityName ---

var col = ["ObjectID", "CustomerKey", "Program.ObjectID", "Name"];

var req = api.retrieve("Activity", col, {

Property: "Name",

SimpleOperator: "equals",

Value: activityName

});

results = req.Results[0];

var programId = results.Program.ObjectID;

//--- get automation name using programId ---

var cols1 = ["ProgramID", "Name", "IsActive", "CategoryID"];

var filter1 = {

Property: "ProgramID",

SimpleOperator: "equals",

Value: programId

};

var request = api.retrieve("Automation", cols1, filter1);

var AutomationName = request.Results[0].Name;

In the provided code snippet, we possess the activity name. Our objective is to determine the corresponding parent automation and its folder location based on this information. Although this process may appear somewhat intricate, it is significantly faster than querying any Out-of-the-Box (OOTB) Data View table.

var result = Platform.Function.Lookup('_automationactivityinstance','AutomationName', 'ActivityName', customerKey);

While it is possible to identify the automation name using a data view (the example shown above), this approach is time-consuming due to the need to query a substantial amount of data.

Example Scenario:

To achieve the desired outcome, follow these steps:

Input: Provide the target Data extension (DE) name.
Execute the code: Running the code will yield results containing all queries along with their parent automation names and their respective folder paths.
Validation: The code also includes a small validation check. If an invalid or nonexistent DE name is provided, corresponding error messages will be displayed.

Feel free to proceed with these instructions, and let me know if you need further assistance! 😊

The Full Code:

Platform.Load("Core", "1");

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

var Qname, FolderID

var results = [];

var opts = null;

var props = null;

//--- enter your DE name here (start) ---

var deName = "<your DE name goes here>";

//--- enter your DE name here (end) ---

try {

var customerKey = GetDeKey(deName);

if (customerKey) {

Write("<b>" + "Queries which updating data to '" + deName + "' DE: " + '</b>');

var filter = {

Property: "DataExtensionTarget.CustomerKey",

SimpleOperator: "equals",

Value: customerKey

};

var cols = ["Name", "CustomerKey", "CategoryID"];

var data = api.retrieve("QueryDefinition", cols, filter);

var id = 0;

if (data.Results.length > 0) {

for (var i = 0; i < data.Results.length; i++) {

var Qname = data.Results[i].Name;

var extKey = data.Results[i].CustomerKey;

if (Qname) {

var AutomationObj = GetAutomation(Qname);

var automationPath = GetFolderPath(AutomationObj.FolderID);

id++;

Write("<br>" + "Query-" + id + ": " + Qname + " | Automation Name: " + AutomationObj.AutomationName + " | Automation Folder: " + automationPath.join(" > "));

}

else {

Write("<br>Queries not found!");

}

else {

Write("please enter a valid DE name");

}

catch (e) {

// Write("<br><br><br>" + "Exception:" + Stringify(e));

}

function GetDeKey(deName) {

var deKey;

var results;

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

var req = prox.retrieve("DataExtension", ["CustomerKey"], {

Property: "Name",

SimpleOperator: "equals",

Value: deName

});

results = req.Results;

deKey = results[0].CustomerKey;

return deKey;

}

function GetAutomation(activityName) {

//--- get program ID using activityName ---

var col = ["ObjectID", "CustomerKey", "Program.ObjectID", "Name"];

var req = api.retrieve("Activity", col, {

Property: "Name",

SimpleOperator: "equals",

Value: activityName

});

results = req.Results[0];

var programId = results.Program.ObjectID;

//--- get automation name using programId ---

var cols1 = ["ProgramID", "Name", "IsActive", "CategoryID"];

var filter1 = {

Property: "ProgramID",

SimpleOperator: "equals",

Value: programId

};

var request = api.retrieve("Automation", cols1, filter1);

var AutomationName = request.Results[0].Name;

var FolderID = request.Results[0].CategoryID;

var obj = {

AutomationName: AutomationName,

FolderID: FolderID

}

return obj;

}

function GetFolderPath(folderId) {

var list = [];

var path = function (id) {

if (id > 0) {

var results = Folder.Retrieve({ Property: "ID", SimpleOperator: "equals", Value: id });

list.unshift(results[0].Name);

var parentID = results[0].ParentFolder.ID;

if(parentID)

return path(parentID);

} else {

return id;

};

}

path(folderId);

return list;

}

</script>

If you have any feedback or comments, please feel free to connect with me on LinkedIn profile. I’m always eager to connect and engage in meaningful discussions! 😊👍

Back

By Ameen Rahman

Streamline Your Search: SSJS solution to Identify Query Activities Targeting a Specific Data Extension

The Full Code:

© www.salesforceguide.me