Upsert / update not working

[remyo1]

We currently have a older version of devart installed which has some flaws (unable to run concurrent downloads from salesforce, unable to run in 64 bits)
I downloaded the trial version for some local testing to see if these issues have been resolved. Good to see they have but i found a new issue.

I tested the upsert and update functionality. I took account and asset from SF and downloaded them to a local DB. Then i tried to upsert the id and the name within the SF table.

I get validation errors in the package.

Error at dataflow task [Devart Salesforce Destination]: The number of input columns for Devart Salesforce Destination.Inputs[input] cannot be zero.

I have been able to do the same with a different connector so it should work.

Need help here.

[Pinturiccio]

Please provide us with the following information:
1. The steps you perform for creating your package;
2. As far as we understand, you use Salesforce as Data Source. What database do you use as Destination?
3. Send us the creation script or description of creation of your table. We need this info for reproducing the situation in our environment.
4. The version of dotConnect for Salesforce that was installed earlier, and the version you are currently using.
5. If possible, email us with data of the table.

[remyo1]

No salesforce is my destination
Input SQL Server which is installed locally

Input Query is: SELECT [id], [name] from Remy_Test_Devart_Acc. Create table statement is below.

Then i do a basic mapping:

ID maps to ID
Name maps to Name

Custum properties i tried selecting update and upsert. Buth SSIS keeps saying the package is invalid.



CREATE TABLE [dbo].[Remy_Test_Devart_Acc](
[AFS_Referrer_Classification_] [nvarchar](255) NULL,
[AFS_Referrer_IMS_Score_] [float] NULL,
[AF_Solutions_] [bit] NULL,
[ANZ_Coronary_] [bit] NULL,
[AccountSource] [nvarchar](40) NULL,
[Account_Active] [bit] NULL,
[Account_Address_Line_1] [nvarchar](100) NULL,
[Account_Address_Line_2] [nvarchar](100) NULL,
[Account_Address_Line_3] [nvarchar](100) NULL,
[Account_Category] [nvarchar](100) NULL,
[Account_City] [nvarchar](100) NULL,
[Account_Country] [nvarchar](255) NULL,
[Account_Postal_Code] [nvarchar](20) NULL,
[Advanced_Energy_Bool_] [bit] NULL,
[Advanced_Energy_Last_Call_Date] [datetime] NULL,
[Apollo_segmentation_] [nvarchar](255) NULL,
[Ardian_] [bit] NULL,
[Ardian_Tender_Type] [nvarchar](255) NULL,
[BKP_] [bit] NULL,
[BKP_Last_Call] [datetime] NULL,
[BillingCity] [nvarchar](40) NULL,
[BillingCountry] [nvarchar](80) NULL,
[BillingLatitude] [float] NULL,
[BillingLongitude] [float] NULL,
[BillingPostalCode] [nvarchar](20) NULL,
[BillingState] [nvarchar](80) NULL,
[BillingStreet] [nvarchar](255) NULL,
[Biologics_] [bit] NULL,
[Biologics_Last_Call] [datetime] NULL,
[Buying_Driver_] [nvarchar](max) NULL,
[CBT_Tender_Type] [nvarchar](255) NULL,
[CRDM_] [bit] NULL,
[CRDM_Account_Segment] [nvarchar](max) NULL,
[CRDM_Activities] [nvarchar](max) NULL,
[CRDM_Carelink] [bit] NULL,
[CRDM_Carelink_] [bit] NULL,
[CRDM_Clinical_Studies] [nvarchar](255) NULL,
[CRDM_Decision_Making_Unit] [nvarchar](255) NULL,
[CRDM_Decision_Power] [nvarchar](255) NULL,
[CRDM_Discovery_Link_Logins_Last_FQ] [float] NULL,
[CRDM_IHMT_ID] [nvarchar](15) NULL,
[CRDM_Implantables_] [bit] NULL,
[CRDM_New_Carelink_Patients_Last_FQ] [float] NULL,
[CRDM_Product_Influence_] [nvarchar](max) NULL,
[CRDM_Productivity_Suite_Account] [bit] NULL,
[CRDM_Trainer_] [bit] NULL,
[CRT_D_Referrer_Classification_] [nvarchar](255) NULL,
[CRT_D_Referrer_IMS_Score_] [float] NULL,
[CVG_Account_Type] [nvarchar](255) NULL,
[CVG_Platinum_Account] [bit] NULL,
[CVG_Segment_Behavior] [nvarchar](255) NULL,
[CVG_Segment_Size] [nvarchar](255) NULL,
[CVG_Tier] [nvarchar](255) NULL,
[CV_MDT_Perception_] [nvarchar](255) NULL,
[CV_Program_Involvement_] [nvarchar](max) NULL,
[Cardio_Vascular_] [bit] NULL,
[Carelink_Base_Brady] [float] NULL,
[Carelink_Base_SQDM] [float] NULL,
[Carelink_Base_Tachy] [float] NULL,
[Carelink_Total_Patient_Base] [float] NULL,
[Cath_Labs] [float] NULL,
[Cathether_Based_Therapies_] [bit] NULL,
[Contact_Department_] [nvarchar](255) NULL,
[Contact_Gender_] [nvarchar](255) NULL,
[Contact_Job_Title_] [nvarchar](255) NULL,
[Contact_Primary_Specialty_] [nvarchar](255) NULL,
[Contact_Secondary_Specialty_] [nvarchar](255) NULL,
[Contact_Segment_] [nvarchar](max) NULL,
[Contact_Segment_CRDM_] [nvarchar](max) NULL,
[Contact_Tertiary_Specialty_] [nvarchar](255) NULL,
[Contract_Type_] [nvarchar](255) NULL,
[Core_Spine_] [bit] NULL,
[Core_Spine_Last_Call] [datetime] NULL,
[Coronary_] [bit] NULL,
[Coronary_Comp_Clin_Trial_With] [nvarchar](max) NULL,
[Coronary_Tender_Type] [nvarchar](255) NULL,
[CreatedById] [nvarchar](18) NULL,
[CreatedDate] [datetime] NULL,
[CurrencyIsoCode] [nvarchar](3) NULL,
[DBS_Epilepsy_Segmentation_Details] [nvarchar](255) NULL,
[DBS_MvD_Referrer_Classification_] [nvarchar](255) NULL,
[DBS_MvD_Segmentation_Details] [nvarchar](255) NULL,
[DBS_Psych] [nvarchar](255) NULL,
[DBS_Psych_Referrer_Classification_] [nvarchar](255) NULL,
[DBS_device_competition] [nvarchar](255) NULL,
[DIB_Account_Type] [nvarchar](255) NULL,
[DIB_SAP_SR_Name] [nvarchar](80) NULL,
[DIB_SAP_Sales_Rep_Id] [nvarchar](10) NULL,
[DIB_Sales_Force_Id] [nvarchar](15) NULL,
[DIB_Sales_Rep] [nvarchar](18) NULL,
[DIB_Sales_Rep_Manually] [bit] NULL,
[Deformity_] [bit] NULL,
[Degenerative_] [bit] NULL,
[Description] [nvarchar](max) NULL,
[DiB_Diabetes_Brand_Decision_Maker_] [bit] NULL,
[DiB_Patient_Account] [bit] NULL,
[DiB_Therapy_Decision_Maker_] [bit] NULL,
[Diabetes] [bit] NULL,
[Diabetes_Core_] [bit] NULL,
[Diabetes_Core_Last_Call_] [datetime] NULL,
[Diabetes_Core_Total_Call_] [float] NULL,
[Diabetes_Last_Call] [datetime] NULL,
[ECT_] [bit] NULL,
[ECT_Tender_Type] [nvarchar](255) NULL,
[ENT_NT_] [bit] NULL,
[ENT_NT_Last_Call] [datetime] NULL,
[EUR_Buying_Group_ID_Text] [nvarchar](50) NULL,
[EUR_CPT_CGMS_Certification_Date_] [datetime] NULL,
[EUR_CPT_Pump_Certification_date_] [datetime] NULL,
[EUR_CRDM_Focus_Text] [nvarchar](max) NULL,
[EUR_CV_CBT_Comp_Clinical_Trial_Text] [nvarchar](max) NULL,
[EUR_CV_Focus_Text] [nvarchar](max) NULL,
[EUR_CV_TAVI_Nr] [float] NULL,
[EUR_Neuro_DBS_] [bit] NULL,
[EUR_Neuro_DBS_Last_Call] [datetime] NULL,
[EUR_Neuro_Pain_Stim_] [bit] NULL,
[EUR_Neuro_Pain_Stim_Last_Call] [datetime] NULL,
[EUR_Neuro_Pumps_] [bit] NULL,
[EUR_Neuro_Pumps_Last_Call] [datetime] NULL,
[Endo_Implanters] [float] NULL,
[Endo_T_E_Need] [nvarchar](255) NULL,
[Endo_Tender_Type] [nvarchar](255) NULL,
[Endovascular_] [bit] NULL,
[Exclude_from_sales_structure_alignment] [bit] NULL,
[Fax] [nvarchar](40) NULL,
[FirstName] [nvarchar](40) NULL,
[GU_Bladder_Referrer_Classification_] [nvarchar](255) NULL,
[GU_Bladder_Segmentation_Details] [nvarchar](255) NULL,
[GU_Bowel_Referrer_Classification_] [nvarchar](255) NULL,
[GU_Bowel_Segmentation_Details] [nvarchar](255) NULL,
[GU_Enterra_Segmentation_Details] [nvarchar](255) NULL,
[Gastro_Uro_competition] [nvarchar](255) NULL,
[HST_] [bit] NULL,
[HST_Tender_Type] [nvarchar](255) NULL,
[Hospital_Solutions_Account] [bit] NULL,
[IND_ENT_NT_Bool_] [bit] NULL,
[IND_ENT_NT_Last_Call_Date] [datetime] NULL,
[IND_SQDM_Bool_] [bit] NULL,
[Id] [nvarchar](18) NULL,
[Implant_Center] [bit] NULL,
[IsCustomerPortal] [bit] NULL,
[IsDeleted] [bit] NULL,
[IsPartner] [bit] NULL,
[IsPersonAccount] [bit] NULL,
[Is_it_a_pediatric_center] [bit] NULL,
[Is_it_an_adult_center] [bit] NULL,
[Jigsaw] [nvarchar](20) NULL,
[JigsawCompanyId] [nvarchar](20) NULL,
[Job_Title_Description_] [nvarchar](100) NULL,
[KOL_] [nvarchar](255) NULL,
[Kyphon_Implanter_] [bit] NULL,
[Kyphon_Referrer_] [bit] NULL,
[LastActivityDate] [datetime] NULL,
[LastModifiedById] [nvarchar](18) NULL,
[LastModifiedDate] [datetime] NULL,
[LastName] [nvarchar](80) NULL,
[LastReferencedDate] [datetime] NULL,
[LastViewedDate] [datetime] NULL,
[MDT_Employee_] [bit] NULL,
[MICS_Priority_Account] [bit] NULL,
[MasterRecordId] [nvarchar](18) NULL,
[Melody_Account] [bit] NULL,
[Name] [nvarchar](255) NULL,
[NameLocal] [nvarchar](255) NULL,
[Neuro_Surgery_Bool_] [bit] NULL,
[Neuro_Surgery_Last_Call_Date] [datetime] NULL,
[Neuromodulation_] [bit] NULL,
[NumberOfEmployees] [int] NULL,
[Number_of_ECMO_cases] [float] NULL,
[Number_of_MICS_cases] [float] NULL,
[Number_of_cases_on_pump] [float] NULL,
[Open_Surgery_Procedures] [float] NULL,
[OwnerId] [nvarchar](18) NULL,
[Ownership] [nvarchar](40) NULL,
[PCI] [float] NULL,
[Pain_Stim_FBSS_Referrer_Classification_] [nvarchar](255) NULL,
[Pain_Stim_FBSS_Segmentation_Details] [nvarchar](255) NULL,
[Pain_Stim_Other_Referrer_Classification_] [nvarchar](255) NULL,
[Pain_Stim_Other_S] [nvarchar](255) NULL,
[Pain_Stim_device_competition] [nvarchar](255) NULL,
[Peripheral_] [bit] NULL,
[Peripheral_Tender_Type] [nvarchar](255) NULL,
[PersonContactId] [nvarchar](18) NULL,
[PersonEmail] [nvarchar](80) NULL,
[PersonEmailBouncedDate] [datetime] NULL,
[PersonEmailBouncedReason] [nvarchar](255) NULL,
[PersonFirstNameLocal] [nvarchar](40) NULL,
[PersonLastCURequestDate] [datetime] NULL,
[PersonLastCUUpdateDate] [datetime] NULL,
[PersonLastNameLocal] [nvarchar](80) NULL,
[PersonMailingCity] [nvarchar](40) NULL,
[PersonMailingCountry] [nvarchar](80) NULL,
[PersonMailingLatitude] [float] NULL,
[PersonMailingLongitude] [float] NULL,
[PersonMailingPostalCode] [nvarchar](20) NULL,
[PersonMailingState] [nvarchar](80) NULL,
[PersonMailingStreet] [nvarchar](255) NULL,
[PersonTitle] [nvarchar](80) NULL,
[Phone] [nvarchar](40) NULL,
[Private] [bit] NULL,
[Pumps_ITB_Referrer_Classification_] [nvarchar](255) NULL,
[Pumps_ITB_Segmentation_Details] [nvarchar](255) NULL,
[Pumps_Pain_Referrer_Classification_] [nvarchar](255) NULL,
[Pumps_Pain_Segmentation_Details] [nvarchar](255) NULL,
[Pumps_device_competition] [nvarchar](255) NULL,
[RDN_Referrer_Classification_] [nvarchar](255) NULL,
[RDN_Referrer_IMS_Score_] [float] NULL,
[RDN_Referrer_Potential_] [nvarchar](255) NULL,
[RecordTypeId] [nvarchar](18) NULL,
[Relationship_Status_] [nvarchar](255) NULL,
[Relationship_Strength_] [nvarchar](255) NULL,
[SAP_Account_Type] [nvarchar](100) NULL,
[SAP_Active] [bit] NULL,
[SAP_Channel] [nvarchar](100) NULL,
[SAP_Customer_Group] [nvarchar](150) NULL,
[SAP_ID] [nvarchar](10) NULL,
[SAP_Region] [nvarchar](30) NULL,
[SAP_Sales_Office] [nvarchar](100) NULL,
[SAP_Sales_Org] [nvarchar](100) NULL,
[SF_Id] [nvarchar](30) NULL,
[Salutation] [nvarchar](40) NULL,
[SicDesc] [nvarchar](80) NULL,
[Spine_Biologics_] [bit] NULL,
[Support_for_Medtronic_] [nvarchar](255) NULL,
[Surgical_Technologies_] [bit] NULL,
[Sutureless_Account] [bit] NULL,
[SystemModstamp] [datetime] NULL,
[TAVI_Referrer_Classification_] [nvarchar](255) NULL,
[TAVI_Referrer_IMS_Score_] [float] NULL,
[TCV_Implanters] [float] NULL,
[TCV_Program_Lead] [nvarchar](255) NULL,
[TCV_T_E_Need] [nvarchar](255) NULL,
[T_E_Contract_] [bit] NULL,
[Trauma_] [bit] NULL,
[Tumor_] [bit] NULL,
[Twins_ID_] [nvarchar](50) NULL,
[Type] [nvarchar](40) NULL,
[Uro_Gastro_] [bit] NULL,
[Uro_Gastro_Last_Call] [datetime] NULL,
[Website] [nvarchar](255) NULL,
[iPlan_Account] [bit] NULL,
[isSales_Force_Account] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[Pinturiccio]

I get validation errors in the package.

Error at dataflow task [Devart Salesforce Destination]: The number of input columns for Devart Salesforce Destination.Inputs[input] cannot be zero.

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

We have changed the behaviour: now refreshing on the "Input and Output Proporties" tab of the Devart Salesforce Destination component editor sets Input Columns mapping according to External Columns. We will post here when the corresponding build of dotConnect for Salesforce is available for download.

Prior to this, clicking Refresh freed the mapping and the following error occurred when executing the package without mapping: "The number of input columns for Devart Salesforce Destination.Inputs[input] cannot be zero.". It was necessary to switch to the "Column Mappings" tab in order to restore the mapping. Now, when clicking Refresh, the mapping is cleared and the new mapping for the columns is based on External Columns.

[Pinturiccio]

New build of dotConnect for Salesforce 2.5.779 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/salesf ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=32918

[remyo1]

Great .. now my package start to run but also starts complaining when trying to update

I created a very basic package

Select ID and a Quantity from a table. Write both to an SQL database table

Second package

Select id and Quantity from SQL table and try to update table

Errors are:

[Devart Salesforce Destination] Error: An exception has occured during data insertion, the message returned from the provider is:
Object reference not set to an instance of an object.

[Devart Salesforce Destination [2]] Error: System.Exception: An exception has occured during data insertion, the message returned from the provider is:
Object reference not set to an instance of an object. ---> Devart.Data.Salesforce.SalesforceException: Open failed. See inner exception for details. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Devart.Data.Salesforce.n.ExecuteBulkRequest[TResult](String resourceUri, Object data)
at Devart.Data.Salesforce.n.a(String A_0, g A_1, String A_2, Boolean A_3)
at Devart.Data.Salesforce.cj.d()
--- End of inner exception stack trace ---
at Devart.Data.Salesforce.cj.d()
at Devart.SSIS.Salesforce.SalesforceDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)
--- End of inner exception stack trace ---
at Devart.SSIS.Salesforce.SalesforceDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Devart Salesforce Destination" (2) failed with error code 0x80131500 while processing input "input" (13). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

[Shalex]

We have reproduced the problem and are working on it.

[Shalex]

The bug is fixed. Look forward to the next public build of dotConnect for Salesforce.

[Shalex]

The bug with throwing System.NullReferenceException is fixed in the newest (2.5.784) build of dotConnect for Salesforce.