Defining Data Extract Activities via Script.Util.WSProxy();

Question

I am designing an Archival process for archiving automatically some of my critical DEs. My idea is to define Data Extract activities dynamically using SSJS to store all the info in the SafeHouse FTP server. What I don't know yet is how to implement SSJS code for creating dynamically Data Extract Activities (I just know how to define SQL Query Activities using the QueryDefinition function). Apparently it seems that is possible using Script.Util.WSProxy() and the "performItem" function (performItem("DataExtractActivity",.....)

Do you have any example showing how to do that ? Or any other idea on how to extract the info to be archived into the FTP server ?

Answer 1

There is no way via WSProxy to create any Data Extracts. The Object only allows for Update and Perform. And update is only for the top level Object (like the name of the Extract, the customerkey, etc.). So really, WSProxy will only allow you to perform existing Data Extracts.

This IS useful, but only part of your use case.

To help with the create/update part of it, there is an undocumented REST endpoint that you can use.

Endpoint for Data Extract Creation:

POST /automation/v1/dataextracts
Host: {{subdomain}}.rest.marketingcloudapis.com
Authorization: Bearer {{authToken}}
Content-Type: application/json

{
    "name": "myDataExtract",
    "key": "myDataExtract",
    "description": "",
    "dataExtractTypeId": "{{yourExtractTypeId}}",
    "fileSpec": "myDataExtract.zip",
    "intervalType": 0,
    "dataFields": [
        {
            "name": "ColumnDelimiter",
            "type": "string",
            "value": ","
        },
        {
            "name": "DECustomerKey",
            "type": "string",
            "value": "myDataExtension"
        },
        {
            "name": "HasColumnHeaders",
            "type": "bool",
            "value": "True"
        },
        {
            "name": "TextQualified",
            "type": "bool",
            "value": "True"
        },
        {
            "name": "UsesLineFeed",
            "type": "bool",
            "value": "False"
        }
    ]
}

For the dataExtractTypeId, there is another endpoint you can use to get a full list of them, but the Id for a Data Extension Extract is: bb94a04d-9632-4623-be47-daabc3f588a6.

Endpoint for Data Extract Type ID:

GET /automation/v1/dataextracttypes
Host: {{subdomain}}.rest.marketingcloudapis.com
Authorization: Bearer {{authToken}}
Content-Type: application/json

The other integral piece you will need is where to put the identifier for the DE you want to target. This can be found in the dataFields array inside the DECustomerKey object. Inside the 'value' property, you will write the CustomerKey of your target DE.

It is fairly easy to create the Data Extract and perform it (you can use WSProxy or REST API to start it), see below for examples of Starting a data extract.

Endpoint to start Data Extract:

POST /automation/v1/dataextracts/{{dataExtractObjectID}}/actions/start
Host: {{subdomain}}.rest.marketingcloudapis.com
Authorization: Bearer {{authToken}}

WSProxy for starting Extract:

<script runat=server>
Platform.Load("Core","1.1.1");
var prox = new Script.Util.WSProxy();
var action = "Start";
var props = {ObjectID: "{{DataExtractObjectID"};
var opts = {};
var data = prox.performItem("DataExtractActivity", props, action, opts); 
</script>

One thing of note, is you likely will need to utilize File Transfers as well to move the extracts from the safehouse to the correct FTP directory.