How to delete data extensions if I can't access them?

Question

I just got a client sending me a request that they can't use Marketing Cloud anymore. According to Salesforce support, the reason is because they have 250k data extensions!! (they had a external service connected to MC that would create a new data extension for every single FTP upload).

Regardless of why they should not have done that, I need to figure out a way to delete data extensions older than 1 year. If I try to access them in Marketing Cloud it will just crash.

My first guess is via API but I don't have any information about the Data Extensions (Key, names) and the only criteria I was given is to delete the older ones.

Any suggestions?

Edit

Trying to retrieve a list of the Data Extensions older than a date, I got to this:

POST /Service.asmx HTTP/1.1
Host: mch0y7lgy467zfdm0bv31lkyrxd1.soap.marketingcloudapis.com
Content-Type: application/xml
SOAPAction: Retrieve
User-Agent: PostmanRuntime/7.20.1
Accept: */*
Cache-Control: no-cache
Postman-Token: 93323b08-5fe7-4cec-a3a6-16ec1c1595ad,da58c01f-eacb-47a4-8f26-9c13202e8975
Host: mch0y7lgy467zfdm0bv31lkyrxd1.soap.marketingcloudapis.com
Accept-Encoding: gzip, deflate
Content-Length: 1457
Connection: keep-alive
cache-control: no-cache

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Header>
        <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
            <wsse:UsernameToken>
                <wsse:Username>myAPIUser</wsse:Username>
                <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">MyAPIPassword</wsse:Password>
            </wsse:UsernameToken>
        </wsse:Security>
    </soapenv:Header>
    <soapenv:Body>
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>DataExtension</ObjectType>
                <Properties>CustomerKey</Properties>
                <Properties>Name</Properties>
                <Properties>ObjectID</Properties>
                <Properties>CreatedDate</Properties>
                <Filter xsi:type="ns1:SimpleFilterPart" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
                    <Property>CreatedDate</Property>
                    <SimpleOperator>lessThan</SimpleOperator>
                    <Value>01/01/2019</Value>
                </Filter>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </soapenv:Body>
</soapenv:Envelope>

But I am receiving the following error:

The server cannot service the request because the media type is unsupported.

For a non-developer as I am, the code looks fine (thanks @gortonington) so I'm stuck.

Answer 1

As @MarkusSlabina said, you need to utilize the SOAP API, I just wanted to provide a couple sample envelopes for you to work off of:

Retrieve:

       <RetrieveRequest>
          <ObjectType>DataExtension</ObjectType>
          <Properties>CustomerKey</Properties>
          <Properties>Name</Properties>
          <Properties>ObjectID</Properties>
          <Properties>CreatedDate</Properties>
            <Filter xsi:type="ns1:SimpleFilterPart" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
           <Property>CreatedDate</Property>
           <SimpleOperator>greaterThan</SimpleOperator>
           <Value>01/01/2019</Value>
        </Filter> 
       </RetrieveRequest>

From the retrieve you will need to grab the CustomerKey from each item in results where you can then do a bulk delete inside of the next envelope so it does not require multiple calls per Retrieve.

  <DeleteRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
     <Options></Options>
     <Objects xsi:type="DataExtension">
        <PartnerKey xsi:nil="true"></PartnerKey>
        <ObjectID xsi:nil="true"></ObjectID>
        <CustomerKey>myDE1</CustomerKey>
     </Objects>
     <Objects xsi:type="DataExtension">
        <PartnerKey xsi:nil="true"></PartnerKey>
        <ObjectID xsi:nil="true"></ObjectID>
        <CustomerKey>myDE2</CustomerKey>
     </Objects>
  </DeleteRequest>

If you are unable to complete this through an external service or application, you can create a script for this via WSProxy and SSJS inside of a script activity and then run it in an automation or by itself. There are a couple undocumented REST endpoints you can use to interact with Script Activities if you cannot access the UI. Check out this post on my blog for some examples.

Answer 2

Using the SOAP API you can retrieve all data extensions older than a certain date (using a simple filter) in order to retrieve all the keys. In following calls you can delete all of these data extensions.

In order to fetch all the relevant data extensions you just need to change the filter in the example request of technical article page Retrieve Data Extension Object Using Customer Key

I suggest writing this in a small program/script (for example using NodeJS, Python, etc.) in order to reduce manual work here. For that purpose, you can use one of these SDKs to ease development: Introduction to Marketing Cloud Platform SDKs

Helpful resources:

• SOAP API Documentation - Data Extension Object
• SOAP API Documentation - Delete a Data Extension
• Retrieve Data Extension Object Using Customer Key