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

CSV Failing upsert all of a sudden due to unexpected changes in SF_generate table

Hi Bill,
Need help here. I have 3 csv files I upload into SFDC using sf_generate to overwrite the tables every day. Now a few fields that I never had in my import have appeared when I run the sf_generate for all 3 tables. focusing on the invoice extract. Datecreated is the new field No Nulls. I get this error no matter what I do to the format (/,-,datetime field) io tried them all. The CSV looks fine.

[Invoice Upsert to SFDC [221]] Error: There was an error with Invoice Upsert to SFDC.Inputs[OLE DB Destination Input].Columns[CreatedDate] on Invoice Upsert to SFDC.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

[Invoice Upsert to SFDC [221]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Invoice Upsert to SFDC.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "Invoice Upsert to SFDC.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[Invoice Upsert to SFDC [221]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Invoice Upsert to SFDC.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "Invoice Upsert to SFDC.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

This is a high priority and the integrations are failing and none of my data is going into SDFC.
1 person has
this problem
+1
Reply
  • As a workaround, can you try deleting those columns after the SF_Generate and let your other code run through?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Using Linked Server.
    Here is my SF_Generate command,. Can I just pick the fields I want and ignore the date fields. 0 records coming from CSV into Invoice__C but it always worked before Friday.

    USE [salesforce backups]
    Drop Table [salesforce backups].dbo.Invoice__c_load
    -- Once deleted the SF_generate command takes the structure of the SFDC Order object and
    -- creates a blank local table in SQL
    exec SF_Generate 'upsert','SALESFORCE', 'Invoice__c_load'
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • How many columns total are in the Invoice__c? Also, how many columns on that table do you think are causing the issue? SF_Generate creates a load table based on all fields of the Invoice__c table, so when those fields were added to this object up on Salesforce, the SF_Generate would now pick them up.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Hi,
    I fixed the problem. But here are the facts you requested
    How many columns total are in the Invoice__c? 30
    Also, how many columns on that table do you think are causing the issue? 0 of the existing columns are a problem has worked for 2 years. Its the new columns that appeared from the SF_Generate command(4 of them caused this issue, createdate,lastmodifieddate,createdbyid,lastmodifiedbyid).
    SF_Generate creates a load table based on all fields of the Invoice__c table, so when those fields were added to this object up on Salesforce, the SF_Generate would now pick them up. Yes i get that.
    To Fix: I added 4 drop fields after the genereate command. Ran the SSIS package and it worked.So this is what my SQL looks like now:
    USE [salesforce backups]
    Drop Table [salesforce backups].dbo.Invoice__c_load
    exec SF_Generate 'upsert','SALESFORCE', 'Invoice__c_load'
    alter table dbo.invoice__c_load drop column CreatedDate
    alter table dbo.invoice__c_load drop column LastModifiedDate
    alter table dbo.invoice__c_load drop column CreatedById
    alter table dbo.invoice__c_load drop column LastModifiedbyID
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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