3

Is there a way to get number of records in a Data Extension using API? we tried to run data query get the counts data into a new DE, but how can we use API to call the query activity getting the counts data? thank you!

Thanks for your answer. Usually ,We use some program language such as C#, Node to excute the request, not send such soap message directly. When use FuelSDK-Node-SOAP to perform start action of the query activty. it will occur error. FuelSDK-Node-SOAP : http://salesforce-marketingcloud.github.io/FuelSDK-Node-SOAP/FuelSoap.html#perform

The code we excute:

const FuelSoap = require('fuel-soap');
var SOAPoptions = {
    auth: {

        clientId: '*********',
        clientId: '*********'
    },
    soapEndpoint: 'https://webservice.exacttarget.com/Service.asmx'
};

var SoapClient = new FuelSoap(SOAPoptions);

var def = {
    'CustomerKey': 'RecordCounts',
};

SoapClient.perform('QueryDefinition', def, function (err, response) {
    if (err) {
        console.log('Exception message:'+err.Message);
        console.log('Exception code:' + err.code);
        console.log('Exception stack:' +error.stack);

    } else {
        console.log(response.body.Results);
    }
});

Below is the exception info :

e:\workspace\node_space\hello\node_9a2eda60b6df3.tmp:5
SoapClient.perform('QueryDefinition', def, function (err, response) {
^

ReferenceError: SoapClient is not defined
    at Object.<anonymous> (e:\workspace\node_space\hello\node_9a2eda60b6df3.tmp:5:1)
    at Module._compile (module.js:652:30)
    at Object.Module._extensions..js (module.js:663:10)
    at Module.load (module.js:565:32)
    at tryModuleLoad (module.js:505:12)
    at Function.Module._load (module.js:497:3)
    at Function.Module.runMain (module.js:693:10)
    at startup (bootstrap_node.js:188:16)
    at bootstrap_node.js:609:3

Do you know how to solve this question.

2
  • Do you need these counts inside or outside of Marketing Cloud? – Adam Spriggs Jun 7 '18 at 11:03
  • outside. we building our own hubexchange application and we want to show end user the data extesnion record number used for this application. – StackBruce Jun 7 '18 at 13:16
5

No need to use the official API. Since there's an AMPscript function that returns this data, you could build a simple Cloud Page in your account that returns a JSON payload with this information:

%%[

var @key, @rowCount
set @key = QueryParameter("key")
set @rowCount = iif(empty(@key), 0, DataExtensionRowCount(@key))

]%%{ "rowCount": "%%=v(@rowCount)=%%"}

Working example:

https://pub.s7.exacttarget.com/1lnyuzpi01y?key=DataExtensionTest

Reference

4
  • thank you Adam!! that looks workable. we will take a try!!1 – StackBruce Jun 7 '18 at 14:12
  • Thank you so much Adam, we can get the rowcount of dataextension with the solution you provided, but it just returns text/html. do you know how can we get json format when we submit a get request ? Thanks again! – StackBruce Jun 8 '18 at 2:44
  • That's configurable when you're creating the Cloud Page. Choose a Code Resource type and then JSON. – Adam Spriggs Jun 8 '18 at 12:58
  • thank you Adam! we successfully get this done with your guidance! – StackBruce Jun 9 '18 at 17:11
2

You can retrieve rows count using an undocumented REST endpoint:

GET https://YOUR-HOST.rest.marketingcloudapis.com/data/v1/customobjectdata/key/<DE-KEY>/rowset?$page=1&$pagesize=1
{
  "links": {
    "self": "/v1/customobjectdata/token/100e08a6-6e2d-4e33-9e10-0bf30fc9ebc7/rowset?$page=1",
    "next": "/v1/customobjectdata/token/100e08a6-6e2d-4e33-9e10-0bf30fc9ebc7/rowset?$page=2"
  },
  "requestToken": "100e08a6-6e2d-4e33-9e10-0bf30fc9ebc7",
  "tokenExpireDateUtc": "2020-10-09T12:44:32.51",
  "customObjectId": "<DE-ID>",
  "customObjectKey": "<DE-KEY>",
  "pageSize": 1,
  "page": 1,
  "count": 636,
  "items": [
    {
      "keys": {
        "email": "foo@bar.net"
      },
      "values": {
        "client_id": "ABCDEFGHUJKLMNOPQ",
        "country": "",
        "foo": "",
        "bar": ""
      }
    }
  ]
}
2
  • Does this Request work using SSJS? When I try postman, it works, but not on cloudpage – Swati Mishra Dec 23 '20 at 12:01
  • @SwatiMishra I think it is better to use DataExtensionRowCount(@key) for ssjs – Denis Kurochkin Dec 23 '20 at 18:07
0

As you already created a query that extracts the row-count into a data extension, you just need to run the automation and then read the contents of your data extension that holds the number of rows - both possible using the SOAP-API.

A sample SOAP-request used to trigger an automation (the one containing your SQL query activity) could look like this:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
  <soapenv:Header>
     <wsse:Security soapenv:mustUnderstand="1">
        <wsse:UsernameToken>
           <wsse:Username>Username</wsse:Username>
           <wsse:Password>Password</wsse:Password>
        </wsse:UsernameToken>
     </wsse:Security>
  </soapenv:Header>
  <soapenv:Body>
     <PerformRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <Action>start</Action>
        <Definitions>
           <Definition xsi:type="Automation">
              <Client>
                 <ID>1234567</ID>
              </Client>
              <ObjectID>d3f5e492-f1a9-47be-a510-15b0d8de65d3</ObjectID>
           </Definition>
        </Definitions>
     </PerformRequestMsg>
  </soapenv:Body>
</soapenv:Envelope>

A sample SOAP-request retrieving data from a data extension would look like this:

<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 wsu:Id="UsernameToken-24440876" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
           <wsse:Username>XXXXX</wsse:Username>
           <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">XXXXX</wsse:Password>
        </wsse:UsernameToken>
     </wsse:Security>
  </soapenv:Header>
  <soapenv:Body>
     <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <RetrieveRequest>
           <ObjectType>DataExtensionObject[Example DE]</ObjectType>
           <Properties>ROWCOUNT</Properties>
           <Filter xsi:type="SimpleFilterPart">
              <Property>DENAME</Property>
              <SimpleOperator>equals</SimpleOperator>
              <Value>YOURCOUNTEDDE</Value>
           </Filter>
        </RetrieveRequest>
     </RetrieveRequestMsg>
  </soapenv:Body>
</soapenv:Envelope>

Both of the above examples are from the SOAP-API documentation, however the second one has been slightly modified.

Related information:

0

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.