Create a Query Activity verified

Learn how to create a Query Activity in Salesforce Marketing Cloud (SFMC) with SSJS (server-side JavaScript). Code snippets include Core, WSProxy and REST API methods.

Core

var config = {
    Name: "MyNewQuery",
    CustomerKey: GUID(),
    TargetUpdateType: "Overwrite",
    TargetType: "DE",
    Target: {
        Name: "MyDataExtension",
        CustomerKey: "S0M3-GU1D-K3Y-G03SR1G4T-H3R3"
    },
    QueryText : "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers"
};

var result = QueryDefinition.Add(config);
<script runat="server">

    Platform.Load("core", "1");

	try {

        var config = {
            Name: "MyNewQuery",
            CustomerKey: GUID(),
            TargetUpdateType: "Overwrite",
            TargetType: "DE",
            Target: {
                Name: "MyDataExtension",
                CustomerKey: "S0M3-GU1D-K3Y-G03SR1G4T-H3R3"
            },
            QueryText : "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers"
        };

        var result = QueryDefinition.Add(config);

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	

</script>
"OK"

WSProxy

var config = {
    CustomerKey: GUID(),
    Name: "MyNewQuery",
    Description: "My new query",
    CategoryID: 12345,
    TargetType: "DE",
    TargetUpdateType: "Overwrite",
    QueryText: "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
    DataExtensionTarget: {
        Name: "MyDataExtension",
        CustomerKey: "S0M3-GU1D-K3Y-G03SR1G4T-H3R3"
    }
};

var result = api.createItem("QueryDefinition", config); 
<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();
	
	try {

        var config = {
            CustomerKey: GUID(),
            Name: "MyNewQuery",
            Description: "My new query",
            CategoryID: 12345,
            TargetType: "DE",
            TargetUpdateType: "Overwrite",
            QueryText: "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
            DataExtensionTarget: {
                Name: "MyDataExtension",
                CustomerKey: "S0M3-GU1D-K3Y-G03SR1G4T-H3R3"
            }
        };

        var result = api.createItem("QueryDefinition", config); 

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	

</script>
{
    "Status": "OK",
    "RequestID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "Results": [
        {
            "NewID": 0,
            "NewObjectID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
            "PartnerKey": null,
            "Object": {
                "QueryText": "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
                "TargetType": "DE",
                "DataExtensionTarget": {
                    "Name": "MyDataExtension",
                    "Description": null,
                    "Keyword": null,
                    "Client": null,
                    "PartnerKey": null,
                    "PartnerProperties": null,
                    "CreatedDate": "0001-01-01T00:00:00.000",
                    "ModifiedDate": null,
                    "ID": 0,
                    "ObjectID": null,
                    "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
                    "Owner": null,
                    "CorrelationID": null,
                    "ObjectState": null,
                    "IsPlatformObject": false
                },
                "TargetUpdateType": "Overwrite",
                "FileSpec": null,
                "FileType": null,
                "Status": null,
                "CategoryID": 12345,
                "InteractionObjectID": null,
                "Name": "MyNewQuery",
                "Description": "My new query",
                "Keyword": null,
                "Client": null,
                "PartnerKey": null,
                "PartnerProperties": null,
                "CreatedDate": "0001-01-01T00:00:00.000",
                "ModifiedDate": null,
                "ID": 0,
                "ObjectID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
                "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
                "Owner": null,
                "CorrelationID": null,
                "ObjectState": null,
                "IsPlatformObject": false
            },
            "CreateResults": null,
            "ParentPropertyName": null,
            "StatusCode": "OK",
            "StatusMessage": "QueryDefinition created",
            "OrdinalID": 0,
            "ErrorCode": 0,
            "RequestID": null,
            "ConversationID": null,
            "OverallStatusCode": null,
            "RequestType": "Synchronous",
            "ResultType": null,
            "ResultDetailXML": null
        }
    ]
}

REST API

var query =  'SELECT TOP 10 SubscriberKey, EmailAddress FROM _Subscribers';

var payload = {
    "name": "MyQuery",
    "key": GUID(),
    "description": "My new query",
    "queryText": query,
    "targetName": "MyDataExtension",
    "targetKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetUpdateTypeId": 0,
    "targetUpdateTypeName": "Overwrite",
    "categoryId": 12345
}

var endpoint = restInstanceUrl + "automation/v1/queries";

var request = new Script.Util.HttpRequest(endpoint);
    request.emptyContentHandling = 0;
    request.retries = 2;
    request.continueOnError = true;
    request.setHeader("Authorization", "Bearer " + accessToken);
    request.method = "POST";
    request.contentType = "application/json";
    request.encoding = "UTF-8";
    request.postData = payload;

var results = request.send();

var result = Platform.Function.ParseJSON(String(results.content));
<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();

    var restInstanceUrl = "https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com/",
        accessToken     = "YOUR_REST_API_TOKEN";
	
	try {

        var targetName = "MyDataExtension";

        var request = api.retrieve("DataExtension", ["CustomerKey"], {
            Property: "Name", 
            SimpleOperator: "equals",
            Value: targetName 
        });

        var targetKey = request.Results[0].CustomerKey;

        var request = api.retrieve("DataFolder", ["ID"], {
            LeftOperand: {
                Property: "Name", 
                SimpleOperator: "equals",
                Value: "Query" 
            },
            LogicalOperator: "AND",
            RightOperand: {
                Property: "ContentType", 
                SimpleOperator: "equals", 
                Value: "queryactivity" 
            }
        });

        var categoryId = request.Results[0].ID;

        var query =  'SELECT TOP 10 SubscriberKey, EmailAddress FROM _Subscribers';

        var payload = {
            name: "MyNewQuery",
            key: GUID(),
            description: "My new query",
            queryText: query,
            targetName: targetDE,
            targetKey: targetKey,
            targetUpdateTypeId: 0,
            targetUpdateTypeName: "Overwrite",
            categoryId: categoryId
        }

        var endpoint = restInstanceUrl + "automation/v1/queries";

        var request = new Script.Util.HttpRequest(endpoint);
            request.emptyContentHandling = 0;
            request.retries = 2;
            request.continueOnError = true;
            request.setHeader("Authorization", "Bearer " + accessToken);
            request.method = "POST";
            request.contentType = "application/json";
            request.encoding = "UTF-8";
            request.postData = Stringify(payload);

        var results = request.send();

        var result = Platform.Function.ParseJSON(String(results.content));

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));

    }	

</script>
{
    "queryDefinitionId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "name": "MyNewQuery",
    "key": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "description": "My new query",
    "queryText": "SELECT TOP 10 SubscriberKey, EmailAddress FROM _Subscribers",
    "targetName": "MyDataExtension",
    "targetKey":"S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetDescription": "",
    "createdDate": "2022-12-07T13:05:21.77",
    "modifiedDate": "2022-12-07T13:05:21.77",
    "targetUpdateTypeId": 0,
    "targetUpdateTypeName": "Overwrite",
    "validatedQueryText": "SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\n\r\nINSERT INTO C100000000.[MyDataExtension] ([SubscriberKey], [EmailAddress])\r\nSELECT querydef.[SubscriberKey], querydef.[EmailAddress]\r\nFROM (SELECT TOP 10 SubscriberKey, EmailAddress FROM C100000000._Subscribers) AS querydef \r\nSELECT @rcInsert = @@ROWCOUNT;;\r\n",
    "categoryId": 12345,
    "isFrozen": false
}

WARNING

Error message about a wrong value for the 'name' field means that the name already exists.

WARNING

targetUpdateTypeId and targetUpdateTypeName are both required and need to have a matching Id/Name combination.

Target update mapping

targetUpdateTypeIdtargetUpdateTypeName
0Overwrite
1Update
2Append

Reference

Ressources and references related to the current methods.

Official documentation
SOAP object

Help me turn coffee into code

This website is provided to you free of charge. However, a lot of time and effort are spent to write, test and mainain the code. Please consider supporting my work by buying me a cup of coffee.

Last Updated: