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

After a bulk upsert operation, I'm seeing the Error column populated for data for different rows. Can anyone help?

For example, I'm seeing success for rows that blatantly have invalid email addresses and INVALID_EMAIL_ADDRESS for fields with a completely different email address than the one in the error.

I'm using SQL 2012 SMS with the local instance being SQL Express 2012.
1 person has
this question
+1
Reply
  • Can you post the exact command sf_bulkops you are running ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Exec SF_BulkOps 'Upsert:bulkapi','SALESFORCE','Contact_Load', 'FundraiserContactId__c'

    It seems that if I run this a second time, it outputs the correct errors for all rows. I'd like to get to the bottom of the problem though - we can't go to production having to run twice.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Bill, can I email you? I'm with JustGiving - we're having some problems making DBAmp work. It seems to modify Salesforce data as it should but it has difficulties parsing the result files. Sometimes we get no results at all and sometimes they're in the wrong order.

    I'm certain it's probably me doing something wrong as I can't find anyone else with the same troubles. We need to use this tool to release something to production very soon.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Hi Pete,

    We recently introduced a new way of writing error messages back into the table. I think you have found a bug in that new code.

    Luckily, the old code with 6 years of production use is still available. Just modify your operation to be 'Upsert:bulkapi(old)'

    I believe your issues will resolve when you change to this operation.

    My email is support at forceamp.com . Perhaps later this week we could have a quick call so that I can learn more about the characteristics of your table to determine what is causing the bug.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • OK Bill.

    I need to wait for someone to confirm a restore on that org so I won't re-run the script until tomorrow. I'll let you know how it goes.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • OK, I just ran my initial upsert into Account using:

    Exec SF_BulkOps 'Upsert:bulkapi(old)','SALESFORCE','Account_Load','JG_Charity_ID__c'

    From within Salesforce I can see that out of 15,000 records only 2 failed but in SSMS I'm given this response:

    RUNNING THE UPSERT
    --- Starting SF_BulkOps for Account_Load
    08:47:47: Run the DBAmp.exe program.
    08:47:47: DBAmp Bulk Operations. V2.19.1 (c) Copyright 2006-2014 forceAmp.com LLC
    08:47:47: Upserting Salesforce using Account_Load (DEVLAPTOP-22\SQLEXPRESS / salesforce backups) .
    08:47:48: DBAmp is using the SQL Native Client.
    08:47:51: SF_Bulkops will poll every 60 seconds for up to 3600 seconds.
    08:47:58: Error: SetData for SQL Table failed.
    Source: Microsoft SQL Server Native Client 11.0
    Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    08:47:58: Error: Stopping because unable to create batch.
    08:47:59: 5001 rows read from SQL Table.
    08:47:59: 0 rows processed.
    08:47:59: Job still running.
    08:48:14: Job still running.
    08:49:15: Job still running.
    08:50:16: Job Complete.
    08:50:16: Retrieving Job Status.
    08:50:17: 15000 rows read from SQL Table.
    08:50:17: 15000 rows have no job/batch information (never sent to salesforce).
    08:50:17: 0 rows marked with current status.
    08:50:17: Errors occurred. See Error column of row and above messages for more information.
    08:50:17: Error: DBAmp.exe was unsuccessful.
    08:50:17: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe upsert:bulkapi(old) Account_Load "DEVLAPTOP-22\SQLEXPRESS" "salesforce backups" "SALESFORCE" "JG_Charity_ID__c" " "
    --- Ending SF_BulkOps. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_BulkOps, Line 125
    SF_BulkOps Error: 08:47:47: DBAmp Bulk Operations. V2.19.1 (c) Copyright 2006-2014 forceAmp.com LLC08:47:47: Upserting Salesforce using Account_Load (DEVLAPTOP-22\SQLEXPRESS / salesforce backups) .08:47:48: DBAmp is using the SQL Native Client.08:47:51: SF_Bulkops will poll every 60 seconds for up to 3600 seconds.08:47:58: Error: SetData for SQL Table failed.Source: Microsoft SQL Server Native Client 11.0Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.08:47:58: Error: Stopping because unable to create batch.08:47:59: 5001 rows read from SQL Table.08:47:59: 0 rows processed.08:47:59: Job still running.08:48:14: Job still running.08:49:15: Job still running.08:50:16: Job Complete.08:50:16: Retrieving Job Status.08:50:17: 15000 rows read from SQL Table.08:50:17: 15000 rows have no job/batch information (never sent to salesforce).08:50:17: 0 rows marked with current status.08:50:17: Errors occurred. See Error column of row and above messages for more information.

    When I look at the Id and Error columns they're still empty.

    The table I'm using to load has the following definition:

    CREATE TABLE [dbo].[Account_Load](
    [Name] [nvarchar](255) NULL,
    [Website] [nvarchar](255) NULL,
    [Charity_Email__c] [nvarchar](255) NULL,
    [Charity_reg_number__c] [nvarchar](75) NULL,
    [CW_Package__c] [nvarchar](50) NULL,
    [CW_CharityDisplayName__c] [nvarchar](150) NULL,
    [CW_CharityDescription__c] [nvarchar](1500) NULL,
    [CW_JGJurisdictionCountry__c] [nvarchar](255) NULL,
    [CW_CharityImpactStatementWhat__c] [nvarchar](255) NULL,
    [CW_CharityImpactStatementWhy__c] [nvarchar](255) NULL,
    [CW_ShortCode__c] [nvarchar](150) NULL,
    [CW_AccountStatus__c] [nvarchar](100) NULL,
    [BillingStreet] [nvarchar](255) NULL,
    [BillingCity] [nvarchar](255) NULL,
    [BillingCountry] [nvarchar](255) NULL,
    [BillingState] [nvarchar](255) NULL,
    [BillingPostalCode] [nvarchar](10) NULL,
    [Phone] [nvarchar](20) NULL,
    [CW_AppCountry__c] [nvarchar](100) NULL,
    [JG_Charity_ID__c] [nvarchar](50) NULL,
    [CW_TaxExempt__c] [bit] NULL,
    [CW_IsApproved__c] [bit] NULL,
    [Id] [nchar](8) NULL,
    [Error] [nvarchar](255) NULL
    ) ON [PRIMARY]

    I need to upsert around 50,000 - 100,000 accounts with contacts and custom objects. If I can't get the results retrieved automatically, this is going to be nightmare of a task.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Try changing your id field from nchar(8) to nchar(18).
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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