Topic
3 replies Latest Post - ‏2013-03-17T23:30:13Z by SystemAdmin
asyed
asyed
4 Posts
ACCEPTED ANSWER

Pinned topic Salesforce Query Object Activity

‏2013-03-14T18:41:16Z |
Hi,

I am trying to query data from Salesforce.com using Query Object Activity after which writing them to SQL Server database and although I have set the batch size to 200 on SFDC Query Objects configure Query setting it is still returning the entire data set. I was hoping that it would query the first batch of 200 records & write them to the database and then move on to the next batch till the entire data set is processed so wanted to check if this will not be possible using query object activity and if so any suggestion or alternate option would be very help full.

Thanks,
Arif
Updated on 2013-03-17T23:30:13Z at 2013-03-17T23:30:13Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1250 Posts
    ACCEPTED ANSWER

    Re: Salesforce Query Object Activity

    ‏2013-03-14T19:35:29Z  in response to asyed
    The nature of the salesforce.com connector Query Objects activity is to return all data that satisfies the query. (Internally, the Cast Iron activity executes the saleforce.com query and queryMore APIs until all data is handled or memory/backing disk resources are depleted.)

    You might try having the query objects return only the Ids. Then use a split activity to subset the full set of Ids. Inside the split, use a retrieve objects activity to get the data field values that you need.
    • asyed
      asyed
      4 Posts
      ACCEPTED ANSWER

      Re: Salesforce Query Object Activity

      ‏2013-03-15T10:55:59Z  in response to SystemAdmin
      Thanks, this does help.
      Ideally it would be better if there was an option to have the querymore() method set on the activity rather than it being done internally.
      • SystemAdmin
        SystemAdmin
        1250 Posts
        ACCEPTED ANSWER

        Re: Salesforce Query Object Activity

        ‏2013-03-17T23:30:13Z  in response to asyed
        You could import the Salesforce Partner or Enterprise WSDL into Studio and use the Invoke Service activity to call the query() and queryMore() APIs yourself. This route is a bit more work obviously, as it doesn't do as much of the heavy lifting that the Salesforce activities can.

        Basically, you'd call query() once and if needed, have a While Loop call queryMore() until it returned a "done" boolean of true. Both of these APIs support an option in the request header that allows you to set the requested batch size. However, you are not guaranteed to get a batch of 200 if you request it, as Salesforce may override it for performance reasons.

        If you do need to keep it at or below 200 with consistency using this direct API approach, you could try selecting two or more Long Text custom fields in your SOQL statement which Salesforce will forcibly limit to a batch size of 200 or less. Not really an efficient or graceful solution to the problem though. A custom Visualforce Page or Apex Web service could also do the trick in controlling batch size but then you're getting into custom code and a solution that may start to resemble a Rube Goldberg machine.

        Hope this helps,
        Paul