Help get this topic noticed by sharing it on Twitter, Facebook, or email.

SF_TableLoader failed with There are multiple root elements

We are running SF_TableLoader in production and it threw an error about. "There are multiple root elements".

The call ran successfully in development and test.

This is the call we made:
EXEC [salesforce backups].dbo.SF_TableLoader 'Update:BulkAPI',
'SALESFORCE_PROD',
'Contact_EHRDeceasedMatchedUpdate';

This is the structure of the table. zzzzzz and xxxxx are valid column names but were blocked out.
CREATE TABLE [dbo].[Contact_EHRDeceasedMatchedUpdate](
[id] [nchar](18) NOT NULL,
[zzzzzzzz__c] [varchar](5) NULL,
[xxxxxxxx__c] [nvarchar](50) NULL
) ON [PRIMARY]

There are no duplicate ID columns in the data.

Here's the error:
SF_TableLoader Error: 11:12:01: DBAmpNet2 3.7.8.0 (c) Copyright 2015-2017 forceAmp.com LLC11:
12:01: Parameters: update Contact_EHRDeceasedMatchedUpdate TENHDCTHCRMSQL1 salesforce backups
SALESFORCE_PROD11:12:01: Using the Salesforce BulkAPI.11:12:02:
Warning: BulkAPI operations without a Sort column could perform slowly.
11:12:02: Drop Contact_EHRDeceasedMatchedUpdate_Result if it exists.
11:12:02: Create Contact_EHRDeceasedMatchedUpdate_Result with new structure.
11:12:02: Add Error column.
11:12:02: Drop id column.11:12:02: Add id column.
11:12:02: 11626 rows read from SQL Table
11:12:03: Job 7501R00000appzBQAQ created on salesforce.
11:12:05: Batch 7511R00000mKqIFQA0 created at: 2019-05-09T16:12:03.000Z with 10000 rows.
11:12:05: Error: System.Xml.XmlException: There are multiple root elements. Line 1, position 26.
at System.Xml.XmlTextReaderImpl.Throw(Exception e) at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
at System.Xml.XmlReader.ReadToFollowing(String name) at DBAmpNet2.LoaderBulkAPI.SendBatch(MemoryStream stream, Int32 currentSize)
at DBAmpNet2.LoaderBulkAPI.CreateBatches() at DBAmpNet2.Program.HandleBulkOpsOldBulk(String currentOperation, Options currentOptions,
DBAmpRegistry currentDBAmpRegistry) at DBAmpNet2.Program.Main(String[] args)11:12:05: DBAmpNet2 Operation FAILED.
1 person has
this question
+1
Reply
  • Can you go up to the Bulk Monitoring page on Salesforce and find the job with the job Id of 7501R00000appzBQAQ, then can you look at the second batch and see what the state message says.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Does it fail with the same error every time you run it in production?

    We think the issue above is there is an error trying to send the second batch up to Salesforce and Salesforce is sending back a corrupted XML message that our reader is blowing up on.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Yes, it continues to fail.

    I have it coded to re-try if it does not find Operation Successful messages in the RESULT table. It tried 4 times.

    Then I also ran it manually once and it had the same failure.....the message I pasted above.

    This is the first time we have posted > 10000 records in production to the CONTACT table. We have done 50k in DEV and TEST to the CONTACT table.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Are the DEV and TEST environments setup the same as the Production environment as far as DBAmp is concerned?

    If you try: 'Update:BulkAPI2' as the operation, do you see the same error?

    If you do, try: 'Update:soap' as the operation, does that work?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Yes, DBAMP is set up the same across environments.

    There was some concern from the salesforce admins on the number of calls if we don’t use Bulk API.

    How many records get processed at a time with soap and BulkAPI2?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • With the BulkAPI2, Salesforce does all the batching on their backend, so I'm not sure, just whatever their job system decides to do.

    With the soap it is batch sizes of 200.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • I tried the BULKAPI and BULKAPI2 with 100 and 1000 and 2000 and 3000 and 4000 and 5000 and 9999 records.

    The only ones that sent back a clean success message were 100 and 1000 records.

    The Salesforce guys looked on their side and it looks like the batches were processed.

    When I called with more than 1000 records with either BULKAPI of BULKAPI2 I got this Json message:

    --- Starting SF_TableLoader for Contact_EHRDeceasedMatchedUpdate V3.7.8
    21:44:27: Run the DBAmpNet2.exe program.
    21:44:28: DBAmpNet2 3.7.8.0 (c) Copyright 2015-2017 forceAmp.com LLC
    21:44:28: Parameters: update Contact_EHRDeceasedMatchedUpdate TENHDCTHCRMSQL1 salesforce backups SALESFORCE_PROD
    21:44:28: Using the Salesforce bulkapi2 API.
    21:44:29: Warning: BulkAPI operations without a Sort column could perform slowly.
    21:44:29: Drop Contact_EHRDeceasedMatchedUpdate_Result if it exists.
    21:44:29: Create Contact_EHRDeceasedMatchedUpdate_Result with new structure.
    21:44:29: Add Error column.
    21:44:29: Drop id column.
    21:44:29: Add id column.
    21:44:29: 5000 rows read from SQL Table.
    21:44:29: Job 7501R00000apyFHQAY created on salesforce.
    21:44:30: Error: Newtonsoft.Json.JsonReaderException: Unexpected character encountered while parsing value: , line 0, position 0. at Newtonsoft.Json.JsonTextReader.ParseValue() at Newtonsoft.Json.JsonReader.ReadForType(JsonContract contract, Boolean hasConverter) at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent) at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType) at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings) at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings) at DBAmpNet2.LoaderBulkAPI2.CloseJob() at DBAmpNet2.Program.HandleBulkOpsBulk(String currentOperation, Options currentOptions, DBAmpRegistry currentDBAmpRegistry) at DBAmpNet2.Program.Main(String[] args)21:44:30: DBAmpNet2 Operation FAILED.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • This is the error from the BULKAPI (not BULKAPI2) call:

    SF_TableLoader Error: 16:06:13: DBAmpNet2 3.7.8.0 (c) Copyright 2015-2017 forceAmp.com LLC16:06:13: Parameters: update Contact_EHRDeceasedMatchedUpdate TENHDCTHCRMSQL1 salesforce backups SALESFORCE_PROD16:06:13: Using the Salesforce BulkAPI.16:06:14: Warning: BulkAPI operations without a Sort column could perform slowly.16:06:14: Drop Contact_EHRDeceasedMatchedUpdate_Result if it exists.16:06:14: Create Contact_EHRDeceasedMatchedUpdate_Result with new structure.16:06:14: Add Error column.16:06:14: Drop id column.16:06:14: Add id column.16:06:14: 5000 rows read from SQL Table.16:06:15: Job 7501R00000apuJyQAI created on salesforce.16:06:16: Batch 7511R00000mKvWlQAK created at: 2019-05-09T21:06:15.000Z with 5000 rows.16:06:16: Error: Newtonsoft.Json.JsonReaderException: Unexpected character encountered while parsing value: , line 0, position 0. at Newtonsoft.Json.JsonTextReader.ParseValue() at Newtonsoft.Json.JsonReader.ReadForType(JsonContract contract, Boolean hasConverter) at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent) at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType) at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings) at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings) at DBAmpNet2.LoaderBulkAPI.CloseJob() at DBAmpNet2.Program.HandleBulkOpsOldBulk(String currentOperation, Options currentOptions, DBAmpRegistry currentDBAmpRegistry) at DBAmpNet2.Program.Main(String[] args)16:06:16: DBAmpNet2 Operation FAILED.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Is it possible that you are using a service or service that is interfering with the api message flow? For example are you using Cipher cloud or an enterprise messaging bus?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • These are similar errors to the ones you were having earlier with the No URI messages. I strongly suspect something is interfering with the response.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • I think I have the solution. I need to specify a smaller batch size (it defaulted to 10k).

    Will this use the BULKAPI (not SOAP) ?

    EXEC [salesforce backups].dbo.SF_TableLoader 'Update:batchsize(1000)',
    'SALESFORCE_PROD',
    'Contact_EHRDeceasedMatchedUpdate';
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • You need to specify bulkapi

    'update:bulkapi,batchsize(1000)'

    I still believe there is some other tool like cipher cloud that is present in production but not test.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Thank you for the idea of another tool interfering. I will asked the salesforce guys if those tools ring a bell.

    Are the tools like cipher cloud something in the salesforce environment? Or typically another area of the infrastructure stack ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • They would be in your network infrastructure stack.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • We consistently fail with batchsize > 1000 records due to a bad message coming back from SF. When we check the SF batch it really completed ok, but we just don't get the properly formed message back from SF. If we reduce the bulkapi batchsize to 1000 it processed correctly almost every time and we receive a clean message back from SF.

    Is there a way to view the message coming back from SF after processing a batch within a job to see if the SF response is malformed or corrupt?

    How do I know if the batch size must be decreased due to APEX limitations?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • We continue to think that large request messages are causing a problem with some piece of network infrastructure.

    The network team should be able to hook up a Wireshark or Fiddler trace to look at the internal network message that is being returned. If they need help in setting this up, we can get on a call.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated