1

I'm working on a custom activity for JourneyBuilder in nodeJs and I need to make an SQL request like in automation studio to make a process easier. I found the package node-saleforce where you can do SQL but it seems impossible to reach dataExtensions of marketing cloud :

var records = [];
conn.query("SELECT Id, Name FROM Account", function(err, result) {
if (err) { return console.error(err); }
console.log("total : " + result.totalSize);
console.log("fetched : " + result.records.length);
});

Is there a SOAP or REST call to make this possible pls ? If you have an other method to make this easier, I would be glad to see it. Thanks in advance :)

2

If you need to do the same, I found this SOAP call that enable us to create SQL query with a XML file like this :

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Header>
        <Security xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
            <UsernameToken>
                <Username>USERNAME_GOES_HERE</Username>
                <Password>PASSWORD_GOES_HERE</Password>
            </UsernameToken>
        </Security>
    </Header>
    <Body>
        <CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <Options>
            </Options>
            <Objects xsi:type="QueryDefinition">
                <PartnerKey xsi:nil="true">
                </PartnerKey>
                <ObjectID xsi:nil="true">
                </ObjectID>
                <CustomerKey>New Query Key</CustomerKey>
                <Name>New Query</Name>
                <Description>Some Description</Description>
                <QueryText>Select EMAIL_ADDRESS as EMAIL from [Example DE]</QueryText>
                <TargetType>DE</TargetType>
                <DataExtensionTarget>
                    <PartnerKey xsi:nil="true">
                    </PartnerKey>
                    <ObjectID xsi:nil="true">
                    </ObjectID>
                    <CustomerKey>FIRST SEND</CustomerKey>
                    <Name>First Send</Name>
                </DataExtensionTarget>
                <TargetUpdateType>Overwrite</TargetUpdateType>
            </Objects>
        </CreateRequest>
    </Body>
</Envelope>

I then used the package 'easy-soap-request' that enable to send SOAP request like this :

const soapRequest = require('easy-soap-request');

// example data
const url = 'https://webservice.s10.exacttarget.com/Service.asmx';
const headers = {
  'Content-Type': 'text/xml;charset=UTF-8',
  'soapAction': 'Create'
};
const xml = fs.readFileSync('backend/CreateQueryDefinition.xml', 'utf-8');
console.log('XML : '+xml);
// usage of module
(async () => {
  const { response } = await soapRequest(url, headers, xml);
  const { body, statusCode } = response;
  console.log(body);
  console.log(statusCode);
})();

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.