Can you retrieve more than 2500 records with SSJS?

Question

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!

Answer 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.

4. Click Create from the toolbar.

5. 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.

6. 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.

7. 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