How to solve issue with salesforce connector

Mayur Patil 5

The previous Salesforce legacy connector allowed SQL queries, which was useful for handling data issues in certain objects. The new connector only supports SOQL queries e.g.,select fields(standard) from xyz ,lacking support for custom field selection or retrieving all fields ( select fields(custom ) from xyz or select fields(all) from xyz).We require the equivalent of select * from xyz but encounter 'outer bounds' errors when using fields(all). field(all ) considered as a unbound and bulk API 2.0 is not support unbounded query.So please suggest any other methods to retrieve all fields using bulk api 2.0

Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-08-12T17:16:46.4333333+00:00

@Mayur Patil Just checking in to see if the below answer provided by @Vinodh247 helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Mayur Patil 5 Reputation points

2024-08-13T09:48:57.87+00:00

@Smaran Thoomu The previous solution isn't helpful. We need to focus solely on using the ADF tool with Bulk API 2.0. Please suggest approaches using these tools, and visual aids would be beneficial. Additionally, a new Salesforce connector compatible with SQL would be highly advantageous.
Vinodh247 18,251 Reputation points

2024-08-13T10:37:21.9066667+00:00
Mayur Patil: Got it. you want only using bulk api 2.0. I have listed out (highlevel) steps for this, pls check and let me know if this will suit your requirement.

Setting Up ADF with Salesforce Bulk API 2.0

-Steps to Configure ADF for Bulk API 2.0

Create a Linked Service

Create a Pipeline

Configure the Copy Activity

Monitor the Pipeline

-Configuring Bulk API 2.0 Workflow:

Create a Job:

Send a POST request to /services/data/sdf.X/jobs/ingest with the job details.

Upload Data:

Use a PUT request to upload data in CSV format to the job's batch endpoint.

Close the Job:

Send a PATCH request to mark the job as complete.

Check Status:

Use a GET request to monitor the job's progress and retrieve results.

try and let me know
Mayur Patil 5 Reputation points

2024-08-13T12:02:29.87+00:00

@Vinodh247 While I appreciate the information, we primarily need guidance specific to ADF. We already have a linked service set up but the new Salesforce connector doesn't support SQL commands. Our goal is to fetch all fields from Salesforce without manually specifying each one. Unfortunately, the field(all) option isn't available because Bulk API 2.0 itself doesn't support it.

We'd greatly appreciate any suggestions on how to retrieve all fields using Bulk API 2.0 within ADF. Additionally advocating for SQL support in the new Salesforce connector would be a significant benefit for our team's workflow
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-08-14T17:09:55.8666667+00:00
@Mayur Patil I understand that you need guidance specific to ADF and that the new Salesforce connector does not support SQL commands.

To retrieve all fields from Salesforce without manually specifying each one, you can use the Salesforce Object Query Language (SOQL) to retrieve all fields for a specific object. You can try use the "describe" call to get the list of fields for the object and then construct a SOQL query to retrieve all fields.

Here is an example of how you can retrieve all fields for the "Account" object using SOQL:

Use the "describe" call to get the list of fields for the "Account" object:

/services/data/vXX.X/sobjects/Account/describe/

Construct a SOQL query to retrieve all fields for the "Account" object:

SELECT Id, Name, BillingCity, BillingCountry, BillingPostalCode, BillingState, BillingStreet, CreatedById, CreatedDate, Description, Fax, Industry, IsDeleted, LastActivityDate, LastModifiedById, LastModifiedDate, MasterRecordId, OwnerId, ParentId, Phone, Rating, ShippingCity, ShippingCountry, ShippingPostalCode, ShippingState, ShippingStreet, Sic, Site, SystemModstamp, Type FROM Account

You can then use this SOQL query in your ADF pipeline to retrieve all fields for the "Account" object.

Regarding the lack of support for the "field(all)" option in Bulk API 2.0, I understand that this can be a limitation.

I hope this helps. If you have any further questions or concerns, please let me know.
phemanth 10,240 Reputation points Microsoft Vendor

2024-08-19T13:53:28.4933333+00:00

@Mayur Patil We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mayur Patil 5 Reputation points

2024-08-23T14:25:59.8366667+00:00

@Smaran Thoomu @phemanth

While we appreciate the bulk API 2.0, we'd prefer to avoid REST APIs. Unfortunately, the current limitations of bulk API 2.0, such as the lack of support for the field(all) and the new connector's restrictions, make it challenging to extract data from our source. To address this, we suggest considering the implementation of SQL commands for the new connector
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-08-23T18:25:52.3066667+00:00

@Mayur Patil Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Mayur Patil 5 Reputation points

2024-08-24T03:17:50.6666667+00:00

@Smaran Thoomu

I didn't anticipate this solution. Could you provide a solution using the existing Bulk API 2.0? We'd like to avoid combining REST API and Bulk API 2.0. Additionally, please ensure the new connector supports SQL queries so we can efficiently retrieve all fields without lengthy methods
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-08-26T16:13:19.43+00:00

@Mayur Patil While the option to use fields(all) is not available, you can still specify the necessary fields in your SOQL query manually. Additionally, suggesting to Salesforce the inclusion of SQL support in future connector updates might be advantageous for numerous users encountering similar challenges.
Mayur Patil 5 Reputation points

2024-08-27T12:14:07.99+00:00

@Smaran Thoomu

Hi, I'm looking for a way to avoid hardcoding field names in my SOQL queries. Since the pipeline runs daily and field changes are frequent, it's inefficient to manually update the query.
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-08-29T09:52:47.37+00:00

@Mayur Patil I agree that this issue looks strange, and I wasn't able to reproduce this issue. If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.

2 answers

Vinodh247 18,251 Reputation points

2024-08-11T06:49:15.8066667+00:00

Hi Mayur Patil,

Thanks for reaching out to Microsoft Q&A.

Consider using third party ETL tools(talend) that integrate with Salesforce. These tools often provide more flexibility in querying and can handle the retrieval of all fields without the limitations imposed by the Bulk API. These tools typically allow you to set up data flows that can automatically handle metadata and construct the necessary queries.

(OR)

Try query tooling in salesforce If you have access to Salesforce Developer tools or the Salesforce Workbench. You can leverage these tools to run SOQL queries more interactively. This allows you to test and refine your queries before implementing them in your application.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Smaran Thoomu 14,875 Reputation points Microsoft Vendor

2024-08-23T18:25:19.3266667+00:00

Hi @Mayur Patil
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .
Issue: The previous Salesforce legacy connector allowed SQL queries, which was useful for handling data issues in certain objects. The new connector only supports SOQL queries e.g.,select fields(standard) from xyz ,lacking support for custom field selection or retrieving all fields ( select fields(custom ) from xyz or select fields(all) from xyz).We require the equivalent of select * from xyz but encounter 'outer bounds' errors when using fields(all). field(all ) considered as a unbound and bulk API 2.0 is not support unbounded query.So please suggest any other methods to retrieve all fields using bulk api 2.0
Solution: While we appreciate the bulk API 2.0, we'd prefer to avoid REST APIs. Unfortunately, the current limitations of bulk API 2.0, such as the lack of support for the field(all) and the new connector's restrictions, make it challenging to extract data from our source. To address this, we suggest considering the implementation of SQL commands for the new connector.
If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to solve issue with salesforce connector

2 answers

Your answer