Create list of Data Extensions that are being used in Data Extension Extracts in Marketing Cloud

Question

I need to create a list of Data Extensions being used in the Data Extension Extract Activities. I tried using the ExtractDefintion but I haven't managed to make it work.

https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/extractdefinition.html

<script runat="server">
Platform.Load("Core", "1");
try {
    //initiate WSProxy
    var prox = new Script.Util.WSProxy();
    //initiate data extension
    var targetDE = 'externalkey'; //pass external key of the target DE      
    var logDE = DataExtension.Init(targetDE);


    
    function getExtract(objectType, cols) {
    var objectType = "ExtractDefinition",
        cols = ["ObjectID", "Description", "Name"],
        moreData = true,
        reqID = null,
        numItems = 0;
    while (moreData) {
        moreData = false;
        var data = reqID == null ? prox.retrieve(objectType, cols) : prox.getNextBatch(objectType, reqID);
        if (data != null) {
            moreData = data.HasMoreRows;
            reqID = data.RequestID;
            if (data && data.Results) {
                for (var i = 0; i < data.Results.length; i++) {
                    if (data.Results[i].ObjectID) {
                        var cols = ["Name", "CustomerKey", "ObjectID"];
                        var filter = {
                            Property: "ObjectID",
                            SimpleOperator: "equals",
                            Value: data.Results[i].ObjectID
                        };
                        var deName = prox.retrieve("DataExtension", cols, filter);
    
                            logDE.Rows.Add({
                                DEName: deName.Results[0].Name,
                                DEKey: deName.Results[0].CustomerKey,
                                ActivityName: data.Results[i].Name,
                                ActivityType: "Extract",
                                ActivityDescription: data.Results[i].Description
                            });
                    }
                    numItems++;
                }
            }
        }
    }
    Write("<br />" + numItems + " total " + objectType + "<br>");
}
    getExtract(objectType, cols)
} catch (e) {
    Write(Stringify(e));
}
</script> 

Answer 1

You likely won't make much progress trying to get this info via SOAP/WSProxy.

But, you can do this in two steps via REST API in your Script Activity. First do a GET on this route:

https://{{yourtenant}}.rest.marketingcloudapis.com/automation/v1/dataextracts/

This returns all of the extracts, like this:

{
    "count": 2,
    "page": 1,
    "pageSize": 25,
    "items": [
        {
            "dataExtractDefinitionId": "28b8e824-325b-40da-abd5-ec6f858dd214",
            "name": "Daily_Tracking_Extract",
            "key": "28B8E824-325B-40DA-ABD5-EC6F858DD214",
            "dataExtractTypeId": "c7219016-a7f0-4c72-8657-1ec12c28a0db",
            "fileSpec": "Daily_Tracking_Extract_%%Month%%_%%Day%%_%%Year%%.zip",
            "createdDate": "2022-09-01T15:02:31.993",
            "modifiedDate": "2022-09-20T09:35:42.873",
            "createdBy": 717380384,
            "modifiedBy": 717380384,
            "intervalType": 0
        },
        {
            "dataExtractDefinitionId": "78b7bae4-3bd8-4bba-afad-e2f2e80ff29c",
            "name": "DataExtension",
            "key": "DataExtension",
            "dataExtractTypeId": "bb94a04d-9632-4623-be47-daabc3f588a6",
            "fileSpec": "Subscriber_Activity_Summary.txt",
            "createdDate": "2023-02-07T11:43:09.877",
            "modifiedDate": "2023-02-07T13:55:45.767",
            "createdBy": 717375270,
            "modifiedBy": 717375425,
            "intervalType": 0
        }
    ]
}

Once you have the dataExtractDefinitionId you can do another GET on this route, specifying the value at the end.

https://{{yourtenant}}.rest.marketingcloudapis.com/automation/v1/dataextracts/{{dataExtractDefinitionId}}

This'll return the details of the extract definition. Data Extension is in the dataFields array:

{
    "dataExtractDefinitionId": "78b7bae4-3bd8-4bba-afad-e2f2e80ff29c",
    "name": "DataExtension",
    "key": "DataExtension",
    "description": "",
    "dataExtractTypeId": "bb94a04d-9632-4623-be47-daabc3f588a6",
    "fileSpec": "Subscriber_Activity_Summary.txt",
    "createdBy": 717375270,
    "modifiedBy": 717375425,
    "intervalType": 0,
    "dataFields": [
        {
            "name": "ColumnDelimiter",
            "type": "string",
            "value": "tab"
        },
        {
            "name": "DECustomerKey",
            "type": "string",
            "value": "Subscriber_Activity_Summary"
        },
        {
            "name": "HasColumnHeaders",
            "type": "bool",
            "value": "True"
        },
        {
            "name": "TextQualified",
            "type": "bool",
            "value": "False"
        },
        {
            "name": "UsesLineFeed",
            "type": "bool",
            "value": "False"
        }
    ]
}