4

I am trying to use a script activity to update a data extension with the statuses of subscriber records. I have managed to get the code working but it will only add 2500 records. I have been reading about retrieving the OverallStatus to see if it produces the value "MoreDataAvailable". From the link below, I can see that you can use the retrieve method to do this. http://help.exacttarget.com/en-US/technical_library/web_service_guide/methods/retrieve/

There's also mention of a ContinueRequest method call. I can't find any documentation that mentions if this could be used with SSJS.

Is it possible to retrieve more than 2500 records with SSJS? Am I on the right track with the code below?

   <script  runat="server">

Platform.Load("core","1");

try {


var myList = List.Init('All Subscribers');
subs = myList.Subscribers.Retrieve({Property:"Status",SimpleOperator:"equals",Value:"Unsubscribed"});
var MasterPublicationList = DataExtension.Init("MasterPublicationList");


do {
 var status = subs.Retrieve(rr);
//write to data extension

for (var i = 0; i < subs.length; i++) {

var subkeyUnsub = subs[i].SubscriberKey; 

MasterPublicationList.Rows.Add({"Subscriber Key":subkeyUnsub,"Publication":"New Publication name","IsSubscribed":"False"});

}


var rr = Platform.Function.CreateObject("RetrieveRequest");
Platform.Function.SetObjectProperty(rr,"ContinueRequest",RequestID);




}while(status.OverallStatus == "MoreDataAvailable")

} catch (ex) {

  Write("An error has occurred: " + Stringify(ex));
}

</script>

The error message I get is:

An error has occurred: {"message":"Object expected: Retrieve","jintException":"Jint.Native.JsException: Exception of type 'Jint.Native.JsException' was thrown.\r\n at Jint.ExecutionVisitor.Visit(MethodCall methodCall)\r\n at Jint.Expressions.MethodCall.Accept(IJintVisitor visitor)\r\n at Jint.ExecutionVisitor.Visit(MemberExpression expression)\r\n at Jint.Expressions.MemberExpression.Accept(IJintVisitor visitor)\r\n at Jint.ExecutionVisitor.Visit(VariableDeclarationStatement statement)\r\n at Jint.Expressions.VariableDeclarationStatement.Accept(IJintVisitor visitor)\r\n at Jint.ExecutionVisitor.Visit(BlockStatement statement)\r\n at Jint.Expressions.BlockStatement.Accept(IJintVisitor visitor)\r\n at Jint.ExecutionVisitor.Visit(TryStatement statement)","description":"Jint.Native.JsException: Object expected: Retrieve\r\nException of type 'Jint.Native.JsException' was thrown. - from Jint\r\n\r\n"}

Many thanks!

  • I don't think this is going to be possible using SSJS. AMPscript SOAP requests would be the only way to do this with lists. – Kelly J Andrews Dec 23 '14 at 14:38
  • Darn! That would have solve all my problems if I could have got that working. Do you happen to know if we can use AMPscript SOAP requests in Script Activities under the Interactions section? – wimpywombat Dec 23 '14 at 15:03
  • You could have it call the AMPscript landing page a script activity. I'll write this up as an answer. – Kelly J Andrews Dec 23 '14 at 15:05
  • Actually, I think there is a better way anyhow... Ill write that up. – Kelly J Andrews Dec 23 '14 at 15:07
  • Thank you @KellyJAndrews! Any help is greatly appreciated. I've been stuck on this for a week :( – wimpywombat Dec 23 '14 at 15:12
1

The underlying technology has put a limit on number of rows, and SSJS is not directly able to get all the records, it would seem.

In order to do what you want, you should actually be using a Query Activity and utilize the _subscribers system data view.

Here is the link to the query activity documentation

Use the following steps to create a query activity:

  1. Click the Interactions tab on the navigation bar.

  2. Click Activities.

  3. Click Query.

The Queries workspace appears.

  1. Click Create from the toolbar.

  2. Complete the information in the Properties section:

Name - The name of the activity. You use this name to identify the activity in the application. Subscribers cannot see the name.

Key - A value you choose that uniquely identifies the activity. You use this value to identify the activity when using the API.

Description - The description of the activity. You use this description to further help identify the activity within the application. Subscribers cannot see the description.

Query - The SQL that makes up the query. You can click the Check Syntax button to check your SQL.

  1. Complete the information in the Target section:

Select data extension to populate - The data extension to contain the result of the query. Update Type - Determines how the system updates the data extension with new data. Value values include:

. Overwrite - The system deletes the existing records in the data extension and adds the results of the query.

. Update - The system updates the existing records in the data extension with information that results from the query and appends non-matching records.

. Append - The system adds the results of the query to the data extension after the existing records.

  1. Click Save.

Queries are basic SQL to access your data extensions, and list data.

Your query might look something like this edited for real world solution

SELECT  SubscriberKey AS 'Subscriber Key', 
             Cast (
                CASE            
                     WHEN Status = 'Unsubscribed'               
                     THEN 'false'               
                     ELSE 'true'             
                     END 
                 as varchar(15))  As 'isSubscribed', 
              Cast(
                 CASE            
                     WHEN Status = 'Unsubscribed'               
                     THEN 'Perspectives'             
                      END as varchar(15))
               As "Publication"   
FROM _subscribers 
WHERE Status = 'Unsubscribed'

In the query activity - you would select your target data extension. You can perform pretty much any normal SQL function, like joins, nested selects, etc.

The SQL support for the Query Activity is based on SQL Server 2005 capabilities.

  • Only SELECT statements to data extension or data views in an account or in the parent account
    • Nested Queries
    • UNION
    • JOIN
    • GROUP BY
  • Conditional Statements
    • IF Constructs
    • CASE Statements
  • Functions
    • Most functions (i.e. MIN, MAX, etc) are supported
    • CAST and CONVERT
  • Unsupported elements
    • Variables
    • Cursors
    • User Defined Functions
    • Transaction and Locking
    • GOTO
    • PRINT
    • Any sp_* stored procedure
    • EXEC
    • Temporary Tables and Common Table Expressions
    • TEXT and IMAGE Functions
  • Thanks for this @KellyJAndrews. I initially thought that a query activity can solve my problem but it does not support the INSERT INTO statement so I can't add the into a certain value in the data extensions unless I retrieved it from elsewhere. For example: SELECT SubscriberKey AS "Subscriber Key" FROM _subscribers WHERE Status = 'Unsubscribed' INSERT INTO MasterPublicationList (Subscriber Key,Publication,IsSubscribed) VALUES (SubscriberKey','New Publication Name','False'); My sql is limited so please let me know if there is a way I can achieve this that I am not thinking of. Thanks! – wimpywombat Dec 23 '14 at 15:47
  • You are able to update the records - matching on subscriber key or email address. Trust me - query is what you are going to want to do. It's ver versatile, and will run before your send. It's the right solution. If you are getting stuck with the syntax, open a new question regarding the SQL statement and we can get that answered. – Kelly J Andrews Dec 23 '14 at 15:51
  • You would need to set up the MasterPublicationList as your target data extension. It's the right direction - I think we just need to get you in the right spot with queries. Try something really simple, and build from there. Ask more questions :) – Kelly J Andrews Dec 23 '14 at 15:55
  • 2
    I got the query to work! Here is my query if anyone is interested: SELECT SubscriberKey AS 'Subscriber Key', Cast (CASE WHEN Status = 'Unsubscribed' THEN 'false' ELSE 'true' END as varchar(15)) As 'isSubscribed', Cast(CASE WHEN Status = 'Unsubscribed' THEN 'Perspectives' END as varchar(15)) As "Publication" FROM _subscribers WHERE Status = 'Unsubscribed' – wimpywombat Dec 23 '14 at 16:37
  • Hi! I would like to know if this limitation still exists as I am facing a similar problem with SSJS. I have elaborated more here: salesforce.stackexchange.com/questions/274369/… It will be quite a job to replace my strategy with Query Activities knowing they are incapable of deleting rows and I am going to need temporary DEs to figure that out. – Disasterkid Aug 21 '19 at 8:29

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.