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

targetUpdateTypeId	targetUpdateTypeName
0	Overwrite
1	Update
2	Append

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.

Buy me a coffee