Row Count Difference between the Web and Copy activity using Salesforce V2 connector

Question

Row Count Difference between the Web and Copy activity using Salesforce V2 connector

Manjeet Acharya 20

Hi! I am using the Salesforce V2 connector to extract data from my salesforce account. When I use the Copy activity, I get around 1200 rows for a certain object. And when I use the Web activity to pass a query that does a Count of Id from that same object, the row count is different. Does anyone know why this happens? Is there a better way to get a row count of the object using the salesforce V2 connector?

Kalyani Kondavaradala 405 Reputation points Microsoft External Staff Moderator

2025-08-08T09:02:28.26+00:00

Hello @Manjeet Acharya,

Greetings for the day!

I understand your concern, have you tried changing the query you have used in the we activity as suggested by @Vinodh247

If you're calling salesforce object via a Web Activity, your URL would look like this:

https://<yourInstance>.salesforce.com/services/data/vXX.0/queryAll?q=SELECT+COUNT(Id)+FROM+tablename+ALL+ROWS

If you still facing same issue, can you please share the exact query which you have used? i hope you have pointed the correct salesforce object for both the copy and web activities.

And also, could you please let me know if you have used both the activities in single or different pipelines?

Please let us know so that we can guide appropriate solution.

Thanks,

Kalyani
Manjeet Acharya 20 Reputation points

2025-08-08T16:03:41.9666667+00:00

Hi! @Kalyani Kondavaradala , I did try using @Vinodh247 's query with the WHERE clause but that was not the row count I was looking for. I also used the "ALL ROWS" command, but that ended in failure because the API end point does not recognize the "ALL" as a keyword and says Malformed Query. But I did solve my problem:I was initially using the "Salesforce V2" connector. This connector seems to be integrated with the Bulk API 2.0 and is especially designed for large amount of data extraction and is faster. But, this Bulk 2.0 API, as per the documentation does not allow for Aggregations and Count in the Lookup/Copy. So, I switched to the "Salesforce Cloud V2" connector in the linked services. After connecting to the "Salesforce Cloud V2" linked services, I was able to use the Lookup activity to query the Count for any object. Looks like the "Salesforce Cloud V2" connector uses something other than the Bulk 2.0 API and allows for Aggregations and Count directly from the Lookup activity. This gave me the correct count and matches with the count of data that I have in my Tables.

The Lookup activity furthermore has a built-in configuration to "Include Deleted objects" and I am able to dynamically configure this value. Thank you, @Kalyani Kondavaradala and @Vinodh247 for your help!
Kalyani Kondavaradala 405 Reputation points Microsoft External Staff Moderator

2025-08-11T07:10:53.2366667+00:00

Hi Manjeet Acharya,

That’s great to hear your issue has been resolved. Thank you for sharing the solution you followed it will surely help others facing a similar issue.

Thanks,

Kalyani

1 answer

Your answer

Kalyani Kondavaradala 405 Reputation points Microsoft External Staff Moderator

2025-08-08T09:02:28.26+00:00

Hello @Manjeet Acharya,

Greetings for the day!

I understand your concern, have you tried changing the query you have used in the we activity as suggested by @Vinodh247

If you're calling salesforce object via a Web Activity, your URL would look like this:

https://<yourInstance>.salesforce.com/services/data/vXX.0/queryAll?q=SELECT+COUNT(Id)+FROM+tablename+ALL+ROWS

If you still facing same issue, can you please share the exact query which you have used? i hope you have pointed the correct salesforce object for both the copy and web activities.

And also, could you please let me know if you have used both the activities in single or different pipelines?

Please let us know so that we can guide appropriate solution.

Thanks,

Kalyani
Manjeet Acharya 20 Reputation points

2025-08-08T16:03:41.9666667+00:00

Hi! @Kalyani Kondavaradala , I did try using @Vinodh247 's query with the WHERE clause but that was not the row count I was looking for. I also used the "ALL ROWS" command, but that ended in failure because the API end point does not recognize the "ALL" as a keyword and says Malformed Query. But I did solve my problem:I was initially using the "Salesforce V2" connector. This connector seems to be integrated with the Bulk API 2.0 and is especially designed for large amount of data extraction and is faster. But, this Bulk 2.0 API, as per the documentation does not allow for Aggregations and Count in the Lookup/Copy. So, I switched to the "Salesforce Cloud V2" connector in the linked services. After connecting to the "Salesforce Cloud V2" linked services, I was able to use the Lookup activity to query the Count for any object. Looks like the "Salesforce Cloud V2" connector uses something other than the Bulk 2.0 API and allows for Aggregations and Count directly from the Lookup activity. This gave me the correct count and matches with the count of data that I have in my Tables.

The Lookup activity furthermore has a built-in configuration to "Include Deleted objects" and I am able to dynamically configure this value. Thank you, @Kalyani Kondavaradala and @Vinodh247 for your help!
Kalyani Kondavaradala 405 Reputation points Microsoft External Staff Moderator

2025-08-11T07:10:53.2366667+00:00

Hi Manjeet Acharya,

That’s great to hear your issue has been resolved. Thank you for sharing the solution you followed it will surely help others facing a similar issue.

Thanks,

Kalyani

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

The Copy activity using Salesforce V2 may implicitly filter records:

Only Active records (i.e., IsDeleted = false) may be included.
SystemModStamp or LastModifiedDate filtering is applied if incremental loading is enabled.
Some lookup or relationship objects behave differently when queried via Copy activity. You can check for any default or explicit filters set in the Source settings or mapping.

Query Scope in SOQL (Web Activity)

Your SOQL query in Web activity might be --> SELECT COUNT(Id) FROM Account

This counts all visible records to the user running the query, which could include:

Archived or deleted records (in Recycle Bin)

Records that do not meet Copy activity's internal filters

Records that are accessible only due to sharing rules or admin privileges

Try adding a WHERE IsDeleted = FALSE to your SOQL to match Copy activity behavior more closely --> SELECT COUNT(Id) FROM Account WHERE IsDeleted = FALSE

Copy Activity May Omit Certain Records

Depending on the object type (standard vs custom), API version, and selected fields, Copy activity may skip:

Records with nulls in required fields

Unreachable fields due to field-level security

Fields requiring special permissions (e.g., formula fields, lookup fields)

API Limitations and Pagination

Salesforce imposes batch limits (default 2000 records per batch), and ADF handles this with pagination internally. If the Copy activity:

Hits a timeout or fails to paginate completely (rare)
Has a schema mismatch
Is using SOQL with LIMIT or WHERE conditions ->this may return few records

Best method to Get Accurate Row Count with Salesforce V2 Connector:

To align your row counts:

Use the Web activity with filtered SOQL -> SELECT COUNT(Id) FROM Account WHERE IsDeleted = FALSE
Alternatively, use a Copy activity with a derived column (ex:RowNumber) and then aggregate using Data Flow, but this is less efficient.

Enable logging and diagnostics on both activities and check:

Exact query being executed

  Any skipped rows due to schema mapping or errors
  
  Use Salesforce Workbench or Developer Console to validate expected count independently.

Please 'Upvote'(Thumbs-up) and 'Accept' as answer if the reply was helpful. This will be benefitting other community members who face the same issue.

Manjeet Acharya 20 Reputation points

2025-08-07T17:48:21.59+00:00

The Copy activity is pulling in more rows than what the Web Activity's Count query is giving me. The Copy activity is copying around 1197 rows but the count from the web activity is giving me 929. The copy activity is working great for me; I want the web activity row count to be equal to what the copy activity is extracting from the salesforce object.

Share via

Row Count Difference between the Web and Copy activity using Salesforce V2 connector

1 answer

Your answer