0

With the help of script activity shared by Eliot here, similarly I am trying to update the target DE as All Subscribers in a SQL query activity.

Here is the updated code I am using in the script, but for some reason it is not working for me. Can someone please suggest what am I doing wrong here?

<script language="javascript" runat="server">
Platform.Load('Core', '1');
var prox = new Script.Util.WSProxy();
var queryStr = '';
queryStr += 'SELECT DISTINCT';
queryStr += '\nsub.SubscriberKey AS [Subscriber Key]';
queryStr += '\nsub.EmailAddress AS [Email Address]';
queryStr += '\nFROM _Sent s';
queryStr += '\nLEFT JOIN _Subscribers sub'; 
queryStr += '\n   ON s.SubscriberKey = sub.SubscriberKey';
queryStr += '\nLEFT JOIN _Open o';
queryStr += '\n   ON s.SubscriberKey = o.SubscriberKey';
queryStr += '\nWHERE o.SubscriberKey IS NULL';
var queryDef = {
CustomerKey: 'QUERY_ACTIVITY_EXTERNAL_KEY', //Replaced with the SQL query activity external key
ObjectID: 'QUERY_ACTIVITY_URL_ID', //Replaced with the SQL query activity URL ID
QueryText: queryStr,
TargetType: 'DE',
TargetUpdateType: 'Update',
DataExtensionTarget: {
  CustomerKey: 'All Subscribers - xxxx', //Replaced with the All Subscribers External Key
  Name: 'All Subscribers'
}
};
var updateQuery = prox.updateItem('QueryDefinition', queryDef);
Write('result: ' + Stringify(updateQuery))
</script>

enter image description here

8
  • with your code, are you trying to update the table "all subscribers", or are you trying to update a query activity that updates the table for you? Your code sample updates a query activity, not the table. (in that, it assumes that the query activity already exists) Jan 16 at 15:55
  • @JonasLamberty you are correct there, Using the script I am trying to replace the current DE "Allsub_Test" with the [All subscribers] DE as my SQL query target DE. However, it seems like the script is not doing it so. I have attached a screenshot for the reference.
    – user144592
    Jan 17 at 5:22
  • aha, you are trying to target All Subscribers with an SQL Query. That is not possible. All Subscribers is not a DE and cannot be targeted via SQL. You have to export your source DE to Safehouse and import to All Subscribers. Jan 17 at 7:42
  • It makes sense. Thanks, @JonasLamberty! Just another quick question: using the above script, I am able to replace the target Data Extension as Auto-suppression list (similarly to what Eliot has done in this video mc.chat/updating-auto-suppression-lists). So, why can't we do it for All Subscribers too? is it because in the backend Auto-suppression list is a DE and All Subscribers is not?
    – user144592
    Jan 17 at 8:12
  • Autosuppression configurations are DEs. All Subscribers List is not. Try retrieving all DEs via API, and you will be able to verify this. Jan 17 at 8:48

1 Answer 1

0

you are trying to target All Subscribers with an SQL Query. That is not possible. All Subscribers is not a DE and cannot be targeted via SQL. You have to export your source DE to Safehouse and import to All Subscribers.

While Autosuppression configurations are DEs, All Subscribers List is not. Try retrieving all DEs via API, and you will be able to verify this. Can Auto-Suppression Configurations be targeted via SQL Query activity?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .