3

I've inherited a SQL Server Information Services (SSIS) package / TaskFactory where the data source is SalesForce. One table has 761 columns, and when I try to map it using a TF Source control, and then hit the 'Preview Data' button, I receive an error message

Failed to execute query successfully, error message was: MALFORMED_QUERY: SOQL statements can not be longer than 20000 characters.

So.... how to handle this? Is there an SOQL equivalent to 'SELECT * FROM...', or do I need to break this data flow up into multiple data flows, with the PK columns in each, and merge them together after?

Thanks in advance.

  • 1
    This SSIS server seems to be giving you lots of grief... – sfdcfox Nov 21 '14 at 22:46
4

SOQL requires field names; it enforces the practice that developers should only be querying fields they need. If you absolutely need to get all fields, say, for replication purposes, consider running a simple SELECT Id FROM ... query, then using a retrieve(FieldList, SObjectType, ID[] recordIds). This will yield better performance for large data sets. Using this method would use two API calls per up to 2,000 records that are retrieved. Also, if you're purely replicating, consider using getUpdated() and getDeleted() instead of using queries.


Edit: In response to the comment, here's some more information.

The retrieve call is a SOAP API call that allows you to retrieve records by their unique key (called Id). The documentation for this call is in the SOAP API Developer's Guide.

You use it in Java, Ruby, Perl, PHP, C#, or any other language that supports SOAP. In input is, as mentioned originally, a list of fields, the type of object to retrieve, and a list of ID values. The result is a SOAP-based response that translates to whatever the SOAP support translates the records to in the native data type (e.g. a class, stdClass, XmlNode, or whatever). The effective type will be an sObject array or list, whatever terminology is valid for your development language.

  • Can you expand on the use of 'retrieve(FieldList, SObjectType, ID[] recordIds', either by spelling it out or providing a link? Haven't used it before. Thanks. – Jim Horn Jan 5 '15 at 14:15
  • @JimHorn Let me know if this helps. – sfdcfox Jan 5 '15 at 16:50

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.