4

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.

  • 1
    It needs to be text/xml not application/xml for content-type – Gortonington Dec 4 '19 at 13:53
  • 1
    Awesome, that worked! It wasn't an option in Postman but I typed and that worked! – Luiz Fernando Xavier Dec 4 '19 at 15:43
3

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.

  • Thank you @MarkusSlabina and @Gortonington. The code is greatly appreciated. I couldn't make it work yet as I'm getting an error WSE012: The input was not a valid SOAP message because the following information is missing: action.. I saw that it should go on the header somewhere and I'll explore it, but would you know if the action here is 'Retrieve'? – Luiz Fernando Xavier Dec 3 '19 at 23:41
  • Retrieve on first and delete action on second. You can pass the 'SoapAction' in the API header, like so: SOAPAction: Retieve – Gortonington Dec 4 '19 at 0:46
  • Check here for more info: salesforce.stackexchange.com/questions/108637/… – Gortonington Dec 4 '19 at 0:49
  • Thanks @Gortonington. I added SOAPAction to the header and that error is gone, but I got a new one: "The server cannot service the request because the media type is unsupported.", which seems to be confirmed here. Maybe I am missing something? – Luiz Fernando Xavier Dec 4 '19 at 10:50
  • Make sure content-type is xml – Gortonington Dec 4 '19 at 11:35
6

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.