Using AMPscript in a Script Activity

Question

We need to update a record in Salesforce using a Script Activity within an Automation. Ideally, I would like to use the AMPscript UpdateSingleSalesforceObject() function to achieve this as there is no equivalent function in SSJS.

I am aware that Script Activities only work with SSJS. I have tried including my AMPscript in a Content Area and getting SSJS to call it. For example:

<script runat="server">
    Platform.Load("Core","1.1.1");
    try {
        var stream = ContentAreaByName("my content area name");
        Write(TreatAsContent(stream));   
    } catch(e) {
        Write(Stringify(e));
    }
</script>

This works in an email, but does not appear to be supported in a Script Activity.

Does anyone know of a solution for this? The only solution I can think of is to create a Connected App in Sales Cloud and then use the Sales Cloud REST API to update the Salesforce record using an SSJS Post() function. I can do this, but would prefer not to, if I can avoid it.

Answer 1

Server-Side JavaScript gives us more flexibility. We can use Ampscript in Script Activities. I have used it. I am not claiming that this is the best solution, but we can do the same using some tricky way like this:

<script runat="server">
Platform.Load("core","1.1.1");

var sfUpdateString;
var subId = "abc123";   // Assuming subkey is available to process

sfUpdateString = '';
sfUpdateString = '%'+'%[SET @status = UpdateSingleSalesforceObject("Subscription__c","'+subId+'"';
sfUpdateString += ',"Opt_in_Email__c","0","Opt_out_Date_Email__c",Format(Now(),"yyyy-MM-ddTHH:mm:ss"))]%'+'%'; 

Platform.Function.TreatAsContent(sfUpdateString);
Write(Platform.Variable.GetValue("@status")); // For display Status (testing purpose)

</script>

There are two main important steps :

Step-1. Create a string variable that contains Ampscript block like this

sfUpdateString = '';
sfUpdateString = '%'+'%[SET @status = UpdateSingleSalesforceObject("Subscription__c","'+subId+'"';
sfUpdateString += ',"Opt_in_Email__c","0","Opt_out_Date_Email__c",Format(Now(),"yyyy-MM-ddTHH:mm:ss"))]%'+'%';

Step-2. Call SSJS function TreatAsContent to renders all included dynamic content.

Here is reference for TreatAsContent with SSJS

Platform.Function.TreatAsContent(sfUpdateString);
Write(Platform.Variable.GetValue("@status")); // For display Status (testing purpose)