Glen Mazza's Weblog

https://glenmazza.net/blog/date/20230422 Saturday April 22, 2023

Marketing Cloud API calls to retrieve all SQL Queries and data extensions

The following SOAP request to the Salesforce Marketing Cloud API, run within SFMC's Postman Collection, will retrieve, in a single SOAP response, all SQL Query Activities in your business unit, including the query text and the data extensions they load:

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>QueryDefinition</ObjectType>
                <Properties>Name</Properties>
                <Properties>TargetUpdateType</Properties>
                <Properties>Description</Properties>
                <Properties>CustomerKey</Properties>
                <Properties>QueryText</Properties>
                <Properties>Status</Properties>
                <Properties>DataExtensionTarget.Name</Properties>
                <Properties>DataExtensionTarget.CustomerKey</Properties>
                <Filter xsi:type="SimpleFilterPart">
                    <Property>Status</Property>
                    <SimpleOperator>equals</SimpleOperator>
                    <!--SimpleOperator>notEquals</SimpleOperator-->
                    <Value>Active</Value>
                </Filter>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>

By filtering on active/not active, you can also retrieve deleted SQL queries. SFMC apparently does a "soft delete" for at least some period of time after a SQL activity is removed, helpful if you need to retrieve something accidentally discarded.

With all the data in a single text-searchable SOAP response, it becomes much easier to efficiently match SQL queries to the data extensions they read from or write to. It can help identify activities missing descriptions or readable customer keys. Using SOAP also allows you to avoid the one-by-one inspecting in the Automation Studio UI with all its annoying confirmation and warning boxes.

The query below retrieves all data extensions in your business unit. Crossing-out those referenced by the SQL queries above can help identify data extensions perhaps no longer needed. Of course, many data extensions are read from and/or written to outside of SQL queries (such as API calls, internal SFMC processing, and SFMC add-ons), so further investigation beyond non-usage in a query activity would be needed before deleting a data extension.

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</fueloauth>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>DataExtension</ObjectType>
                <Properties>ObjectID</Properties>
                <Properties>CustomerKey</Properties>
                <Properties>Description</Properties>
                <Properties>Name</Properties>
                <Properties>IsSendable</Properties>
                <Properties>SendableSubscriberField.Name</Properties>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>

Posted by Glen Mazza in Marketing Cloud at 03:00AM Apr 22, 2023 | Comments[0]

Post a Comment:

Calendar
« March 2024
Sun Mon Tue Wed Thu Fri Sat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Today
About Me
Java Software Engineer
TightBlog project maintainer
Arlington, Virginia USA
glen.mazza at pm dot me
GitHub profile for Glen Mazza at Stack Overflow, Q&A for professional and enthusiast programmers
Blog Search


Blog article index
Navigation
About Blog
Blog software: TightBlog 4.0.0
Application Server: Tomcat
Database: MySQL
Hosted on: Linode
SSL Certificate: Let's Encrypt
Installation Instructions