How to load SQL server data to Salesforce using ODBC

ODBC PowerPack
,
1

Introduction

Loading data from SQL Server to Salesforce using ODBC can be achieved efficiently with the help of the ODBC PowerPack, which provides enhanced capabilities for ODBC connections in SQL. Here’s a brief guide on how to use ODBC PowerPack to accomplish this task:

Prerequisites

ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area.

If you have to load SQL server data to Salesforce using ODBC, then you can use the Salesforce ODBC Driver, below are some details to configure ODBC DSN.

  1. Type odbcad32.exe in your search box and launch the DSN Config utility.
    odbc-data-source-64-bits

  2. If you want access for yourself then stay on User DSN Tab. If you want to grant access to other users then go to the System DSN tab. For SQL Server Integration go to System Tab and add a new System DSN rather than User DSN. Click the New button.
    image

  3. From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys SalesForce Driver].
    image

  4. Now, we need SalesForce Connection. Let’s create it.

    image
    image757×702 42.7 KB

  5. Now, When you see DSN Config Editor with the Zappysys logo first thing you need to do is change the default DSN Name at the top and Click on Preview Tab, Select Table from Tables Dropdown or you can enter or modify a SOQL query, and click on Preview Data.

External Input from MSSQL – INSERT multiple rows from MSSQL to Salesforce

This example shows how to call the INSERT operation which takes multiple rows as input from an external system (i.e. MSSQL, ODBC, OLEDB). In this example, we specified the Driver Type as MSSQL. For other systems (i.e. CSV, API, ORACLE) change driver type to ODBC and adjust connection string/query. Ensure the query output column names are found in the Salesforce Table. EXTERNAL option maps target columns based on input query. To run this example, the service account used to run Data Gateway Service must have the correct permission if you are using Windows authentication.

INSERT INTO Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost; Initial Catalog=tempdb; Integrated Security=true'  --driver-specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select ''Account001'' as Name,''City001'' as BillingCity,1 as NumberofLocations__c  UNION
          select ''Account002'' as Name,''City002'' as BillingCity,2 as NumberofLocations__c  UNION
          select ''Account003'' as Name,''City003'' as BillingCity,3 as NumberofLocations__c '
          --query for external system (i.e. MSSQL). Output column names in the query must match columns found in the target table (i.e. Account).
          --To see all possible columns in Target, select a table from the dropdown on the preview tab
      )
WITH(
       Output=1/*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When the Output option is supplied then an error is not thrown but you can capture the status and message in __RowStatus and __ErrorMessage output columns*/        
    --, EnableBulkMode=1 --use this to invoke Bulk API (usually slower for less number of rows, works better for large dataset 5K+)
    --OR--  
      , EnableParallelThreads=1 --//Send records in multiple threads for faster data processing (200 rows sent in each batch). If you use EnableBulkMode=1 then this option is ignored and 10000 rows are processed in each batch using Job Style API rather than Real Time API (faster for less than a few hundred rows).
      , MaxParallelThreads=6  --//Maximum threads to use for parallel processing
)

Bulk Api - INSERT a large amount of data from an External Source (i.e. MSSQL) to Salesforce

This example shows the use EnableBulkMode option to insert large amounts of data into salesforce (Invoke Job Style Bulk API). By default, 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only a few hundred rows. In this example, we specified the Driver Type as MSSQL. For other systems (i.e. CSV, API, ORACLE) change driver type to ODBC and adjust connection string/query. Make sure the query outputs column names that are found in the Salesforce Table. EXTERNAL option maps target columns based on input query. To run this example, the service account used to run Data Gateway Service must have the correct permission if you are using Windows authentication.

INSERT INTO Account
SOURCE('MSSQL'--external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost; Initial Catalog=tempdb; Integrated Security=true'--driver-specific connection string (i.e. MSSQL, ODBC, OLEDB)	  
      ,'select top 1000000 C_NAME as Name,C_CITY as BillingCity, C_LOC as NumberofLocations__c  From very_large_staging_table'
      )
WITH(
      Output=1/*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When the Output option is supplied then an error is not thrown but you can capture the status and message in __RowStatus and __ErrorMessage output columns*/  
     , EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200)  
   --,MaxRowsPerJob=500000 --//useful to control memory footprint in driver
    --, ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' if you get locking errors then change to Serial*/
)

Bulk Api – DELETE a large amount of data (Read IDs from External Sources)

This example shows the use EnableBulkMode option to delete large amounts of records from salesforce (Invoke Job Style Bulk API). For deletion, you must pass the Id column from the source (if the source has a different name make sure to alias to Id in SQL). By default, 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only a few hundred rows. In this example, we specified the Driver Type as MSSQL. For other systems (i.e. CSV, API, ORACLE) change driver type to ODBC and adjust connection string/query. Ensure the query output column names are found in the Salesforce Table. EXTERNAL option maps target columns based on input query. To run this example, the service account used to run Data Gateway Service must have the correct permission if you are using Windows authentication.

DELETE FROM Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost; Initial Catalog=tempdb; Integrated Security=true'  --driver-specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select top 1000000 Account_ID as Id From very_large_staging_table'
      )
WITH(
       Output=1/*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When the Output option is supplied then an error is not thrown but you can capture the status and message in __RowStatus and __ErrorMessage output columns*/ 
      , EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
    --,MaxRowsPerJob=500000 --//useful to control memory footprint in driver
    --, ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' if you get locking errors then change to Serial*/
)

Bulk Api – UPDATE large amount of data (Read from External Sources)

This example shows the use EnableBulkMode option to update a large amount of data to salesforce (Invoke Job Style Bulk API). For update, you must pass the ID column from the source (if the source has a different name make sure to alias to Id in SQL). By default, 200 rows are written in a single batch but when you use Bulk API mode it sends 10000 rows in a single batch. BulkAPI mode might not give you performance gain if you are writing only a few hundred rows. In this example, we specified the Driver Type as MSSQL. For other systems (i.e. CSV, API, ORACLE) change driver type to ODBC and adjust connection string/query. Make sure the query outputs column names that are found in the Salesforce Table. EXTERNAL option maps target columns based on input query. To run this example, the service account used to run Data Gateway Service must have the correct permission if you are using Windows authentication.

UPDATE Account
SOURCE('MSSQL' --external driver type (MSSQL, ODBC, OLEDB)
      ,'Data Source=localhost; Initial Catalog=tempdb; Integrated Security=true'  --driver-specific connection string (i.e. MSSQL, ODBC, OLEDB)
      ,'select top 1000000 Account_ID as Id, Account_Name as Name,City as BillingCity From very_large_staging_table'
      )
WITH(
       Output=1 /*Other values can be Output='*' , Output=1 , Output=0 , Output='Col1,Col2...ColN'.  When the Output option is supplied then an error is not thrown but you can capture the status and message in __RowStatus and __ErrorMessage output columns*/
      , EnableBulkMode=1 --use Job Style Bulk API (uses 10000 rows per batch rather than 200) 
    --,MaxRowsPerJob=500000 --//useful to control memory footprint in driver
    --, ConcurrencyMode='Default' /* or 'Parallel' or 'Serial' if you get locking errors then change to Serial*/
)

Conclusion

In this short guide, we’ve covered the basic steps to load data from SQL Server to Salesforce using ODBC Salesforce Driver and the ODBC PowerPack. This pack enhances ODBC connectivity and provides additional features for seamless integration. Make sure to check the documentation of the ODBC PowerPack and the Salesforce ODBC driver for any specific configurations or updates.