1

I am attempting to loop through data extension content to dynamically update and perform a query definition. Essentially, I'd like to update and run the same Query Definition synchronously using SSJS but the following code is not working. Is this even possible using the QD functions?

<script language="javascript" runat="server">

    function queryUpdatePerform(qd, Query, UpdateType) {
        qd = QueryDefinition.Init(qd);
        var update_status = qd.Update({
        QueryText: Query, TargetUpdateType: UpdateType
        });
        var perform_status = qd.Perform();
        var status = "{Update:".concat(update_status).concat(", ").concat("Perform:").concat(perform_status).concat("}"); 
        return status;
    }

    var QD_ExternalKey = "qd-externalkey";
    var DE_ContentDetails_ExternalKey = "de-externalkey";
    var contentDetailsDE = DataExtension.Init(DE_ContentDetails_ExternalKey);
    var contentDetails = contentDetailsDE.Rows.Retrieve();

    for (var i = 0; i < contentDetails.length; i++ ) {
        var QueryLogic = contentDetails[i].QueryLogic;
        var Query = "SELECT * FROM [SourceDE] WHERE EmailAddress IS NOT NULL AND ".concat(QueryLogic);

        var status = "";
        var ut = "";
        if (i === 0) {
            ut = "Overwrite";
            status = queryUpdatePerform(QD_ExternalKey, Query, ut); 
        } else {
            ut = "Update";
            status = queryUpdatePerform(QD_ExternalKey, Query, ut);
        }
    }
</script>
  • where you have declared l variable in your code? – Palash Rai Jul 11 '16 at 6:16
  • Hi @PalashRai - this is a copy/paste error. The variable that was "l" should have been "i". – Daniel Jul 11 '16 at 6:20
3

Your code is working perfectly and only have to added SSJS library in top of the code and need to change 'l' variable to 'i'.

<script language="javascript" runat="server">
    Platform.Load("core", "1");
    function queryUpdatePerform(qd, Query, UpdateType) {
        qd = QueryDefinition.Init(qd);
        var update_status = qd.Update({
        QueryText: Query, TargetUpdateType: UpdateType
        });
        var perform_status = qd.Perform();
        var status = "{Update:".concat(update_status).concat(", ").concat("Perform:").concat(perform_status).concat("}"); 
        return status;
    }

    var QD_ExternalKey = "qd-externalkey";
    var DE_ContentDetails_ExternalKey = "de-externalkey";
    var contentDetailsDE = DataExtension.Init(DE_ContentDetails_ExternalKey);
    var contentDetails = contentDetailsDE.Rows.Retrieve();

    for (var i = 0; i < contentDetails.length; i++ ) {
        var QueryLogic = contentDetails[i].QueryLogic;
        var Query = "SELECT * FROM [SourceDE] WHERE EmailAddress IS NOT NULL AND ".concat(QueryLogic);

        var status = "";
        var ut = "";
        if (i === 0) {
            ut = "Overwrite";
            status = queryUpdatePerform(QD_ExternalKey, Query, ut); 
        } else {
            ut = "Update";
            status = queryUpdatePerform(QD_ExternalKey, Query, ut);
        }
    }
</script> 
| improve this answer | |
  • The code is working but not as intended. I'd like it to perform the queries in succession, rather it appears to be performing the next query before the previous is complete. – Daniel Jul 11 '16 at 21:37
  • Ahh correct! Maybe InvokePerform, can over come this issue but not sure. – Palash Rai Jul 14 '16 at 11:34
  • My solution involves the use of Automation Studio so when I run the query through AS using InvokePerform() I can check the status and continue my loop until the Status is marked as no longer running. Reference: salesforce.stackexchange.com/questions/131147/… – Daniel Jul 22 '16 at 18:54

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.