SalesforceException: exceeded 100000 distinct ids

[twillwerth]

Hi,
I've built an SSIS package in that Inserts rows to the Salesforce Attachment table/object. This works great when I tested in my sandbox environment. When I moved to our Production environment, I'm getting this exception "SalesforceException: exceeded 100000 distinct ids" (full details at bottom). It is true that in our Production environment, we have slightly more than 100,000 rows in the Attachment table, where our sandbox did not have as many.

I have found references to some others hitting this exception.
http://boards.developerforce.com/t5/Gen ... d-p/474661
https://getsatisfaction.com/dbamp/topic ... s_required
However these seem to be related to SELECT and I'm only trying INSERT.
I cannot use the workaround of allowing the user account the "view all data" or full admin permissions due to sensitive data. I'm looking into purging some data but that is a business issue that I cannot solve right now.

This can be re-created in the SSIS BIDS designer or at runtime. It may be happening at the ValidateExternalMetadata phase. I tried setting this to false, but the error still happened at runtime.

Is there anything I can do within dotConnect SSIS to avoid this exception?

Platform:
SQL Server 2008 R2 SP2
using dotConnect for Salesforce SSIS Edition 2.3.189

Screenshot:
http://screencast.com/t/EZxw6gvXdgdS

Full error details:
32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 6:35:22 PM Error: 2013-08-30 18:35:35.00 Code: 0xC0047062 Source: DFT - Build and Attach Devart Salesforce Destination [112] Description: System.Exception: Failed to get the schema from data source. Detailed error messages are: exceeded 100000 distinct ids ---> Devart.Data.Salesforce.SalesforceException: exceeded 100000 distinct ids at Devart.Data.Salesforce.b5.InvokeApiCall[T](Func`1 call) at Devart.Data.Salesforce.b5.b(String A_0) at Devart.Data.Salesforce.a9.a(String A_0, String A_1, bk A_2) at Devart.Data.Salesforce.a9.a(Object[] A_0) at Devart.Data.Salesforce.bx.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3) at Devart.Common.i.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3, Boolean A_4) at Devart.Common.i.a(CommandBehavior A_0, Boolean A_1) at Devart.Common.i.c(CommandBehavior A_0) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Devart.Data.Salesforce.bx.a(CommandBehavior A_0) at Devart.SSIS.Salesforce.b.a(String A_0, b1 A_1) at Devart.SSIS.Salesforce.SalesforceDestination.PreExecute() --- End of inner exception stack trace --- at Devart.SSIS.Salesforce.SalesforceDestination.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper) End Error Error: 2013-08-30 18:35:35.00 Code: 0xC004701A Source: DFT - Build and Attach SSIS.Pipeline Description: component "Devart Salesforce Destination" (112) failed the pre-execute phase and returned error code 0x80131500. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:35:22 PM Finished: 6:35:35 PM Elapsed: 12.339 seconds. The package execution failed. The step failed.

[Shalex]

Devart Salesforce Destination uses SalesforceLoader which retrieves inserted IDs after every insert. That's why SELECT executes actually in your scenario.

You have encountered a limitation of Salesforce. Please contact the Salesforce support team.

[twillwerth]

Thanks Shalex, I was hoping for a creative workaround but understand this is beyond your control.