Is it possible to create a parameterized SQL SFMC activity?

Question

I find it hard to believe that based on this documentation, input parameters cannot be passed into a Salesforce MC activity.

I can use a journey builder activity to perform a row update, however I would like to keep all activities within automation studio. Currently I have to combine the journey builder based SQL UPDATE activity.

I would like to pass in a list of record ID's to an automation studio SQL UPDATE query.

If necessary, creating a DE to hold a value from another process, inline, during a journey would work. However this would be highly inefficient.

Are there alternatives?

Answer 1

Yes, this is indeed a bit frustrating.

An alternative could be the following. Disclaimer, I'm not saying this is a very "lean" approach either. You have to judge yourself whether this is worth it, or whether you don't just work with updating values in data extensions.

There is the possibility to dynamically update your query using SSJS Script activity, which involves a bit of legwork around the core implementation, because you can't update queries inside running automations and see an effect.

First you need a "dummy query" like 'SELECT SubscriberKey From _SENT". Put it into an automation which needs to be "ready" (has a starting event) but is not running or scheduled.

Then you need another automation with an SSJS Script activity. The SSJS updates the query activity by its CustomerKey as desired, see below. You can use js variables to create dynamic parts to your query.

The same script can then call the first automation. You can schedule the "caller" automation normally.

Here is some ssjs that defines the queryText in a query Activity. You of course still need to make use of JS variables to make it dynamic:

 Platform.Load("core","1.1.5");
var existingQuery_Key = "xxxxxxx"
var myQueryText = "SELECT * FROM DE"
var qd = QueryDefinition.Init(existingQuery_Key);
var update_status = qd.Update({QueryText: myQueryText});
</script>

here is how you call one automation from SSJS Script: https://sprignaturemoves.com/retrieving-and-starting-an-automation-with-ssjs/