Salesforce ODBC Drivers BULK INSERT / Custom IDs

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
abayd
Posts: 3
Joined: Mon 16 Apr 2018 13:33

Salesforce ODBC Drivers BULK INSERT / Custom IDs

Post by abayd » Mon 16 Apr 2018 14:15

We got the ODBC drivers for basically just reading data from salesforce (SELECT statement) into our SQL Server. The project changed and now we are inserting data into salesforce. Daily Inserts are couple hundred records so there is no issue there.

We have one monthly update that may extend from 10,000-50,000. Currently we tested an INSERT for 12000 rows and it took 1.5 hours before the connection terminated.

1)My question is : are there any best practices documentation (couldn't find any) for inserting "large" data using SSMS? Bulk Insert?

Driver :1.5.13
win server 2012
SQL Server 2014

Query :

INSERT INTO [dsf]...[Opportunity]
(AccountID,Name,StageName,Amount,Probability,CloseDate,Automotive_Classification__c,Channel__c,Customer_Ship_To__c,End_Customer__c,Opportunity_ID__c,Loss_Reason__c,Vapor_Thermal__c,VP_Car_Truck__c,VP_Design_Parent__c,VP_Global_Sales_Sub_Segment__c,VP_Manufacturer__c,VP_Manufacturer_Group__c,VP_Primary_Design_Center__c,VP_Primary_Design_Country__c,VP_Primary_Design_Region__c)
SELECT * FROM
Salesforce.DBO.ODBCTEST


2) can we use custom IDs for inserts like Opportunity_ID__c above instead of having to lookup salesforce db IDs? this is so we can link it to other tables.
Thank you

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Salesforce ODBC Drivers BULK INSERT / Custom IDs

Post by MaximG » Fri 27 Apr 2018 13:00

1. Our driver supports Batch operations according to the documentation: https://docs.microsoft.com/en-us/sql/od ... statements
However, the use of this feature depends on the third-party tool which you are using our driver with. For example, the possibilities of using
Batch operations in the Microsoft SQL Server Management Studio should be described in this product documentation. In our test environment, we tested
operability and speed of our driver during Batch operations work using SQL Server Integration Services (SSIS)
2. Please specify the type which the described Opportunity_ID__c field has. Are any relations used to link other tables when it is created?

abayd
Posts: 3
Joined: Mon 16 Apr 2018 13:33

Re: Salesforce ODBC Drivers BULK INSERT / Custom IDs

Post by abayd » Mon 07 May 2018 17:30

Thank you for your reply. We ended up buying the SSIS component for Salesforce after testing it. The performance was reduced from 2 hours to 10 mins.
I just need to find the documentation for that SSIS component just to make sure we are using it correctly. Do you have any document link that can help me?

Thank you

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Salesforce ODBC Drivers BULK INSERT / Custom IDs

Post by MaximG » Tue 22 May 2018 08:13

In our test environment in Microsoft Visual Studio, we followed the next steps:

1. Create a new Integration Service Project.
2. Drag and drop Data Flow Task from SSIS Toolbox on your Control Flow.
3. Drag and drop "ODBC Source" and "ODBC Destination". Any source available in the ODBC Connection Manager can serve as a data source, as a data receiver, specify the DSN with our driver. These operations are available by right-clicking in the menu "Edit ..."
4. Establish a connection between "ODBC Source" and "ODBC Destination"
5. In the "ODBC Destination" properties, set the "Data Access Mode" - "Table Name - Batch"
6. Run the received Data Flow Task

Post Reply