Importing Data from Salesforce to SQL Server

There are many cases where users would want to import data from Salesforce to the SQL Server such as an external application, reporting, or analysis. This blog will explore how to fetch data from Salesforce using the ComponentOne Salesforce ADO.NET data connector and push the data to SQL Server.

To fetch and insert data from Salesforce to SQL Server, consider the following steps:

• Authentication to Salesforce
• Data & service request limits
• Schema of data
• Creating a relevant table in SQL Server matching above schema
• Fetching data from Salesforce & save to SQL Server table

Now, let's look at each step in detail:

Authentication

Salesforce supports OAuth authentication for external applications that wish to connect to its REST api’s. This document explains how to set up an app to enable OAuth.

The following information is required to connect to Salesforce:

• Token endpoint
• Api URL
• Client ID
• Client decret
• Salesforce username and password
• Security token

Data Size & Service Request Limits

Since the Salesforce system is available online, there are certain limits on result size and the number of calls for load balancing. This document further explains the limits based on the edition and the error when the limits are exceeded.

Schema of Data

The schema of the entity to get data is in Salesforce here. Alternatively, you can get schema information from the connection object of ComponentOne ADO.NET data connector for Salesforce.

For the purpose of this blog, Order data is needed from the last few years, per country. The following fields are required for this report:

• Billing State
• Billing Street
• Billing City
• Billing Postal Code
• Billing Country
• Total Amount
• Effective Date

Here is the schema for above fields as per Salesforce:

Create Table in SQL Server

Based on the schema of the data needed to create a table in SQLServer, data will be pushed to the table below after it is fetched from Salesforce.

CREATE TABLE [dbo].[OrderReport]
(  

[BillingStreet] VARCHAR(MAX) NULL,
[BillingCity] VARCHAR(50) NULL,
[BillingState] VARCHAR(MAX) NULL,
[BillingCountry] VARCHAR(MAX) NULL,
[BillingPostalCode] VARCHAR(50) NULL,
[EffectiveDate] DATETIME NULL,
[TotalAmount] DECIMAL NULL  

)

Fetch Data from Salesforce & Save to SQL Server

Next, create a .NET Core console application and add the following packages from NuGet:

• AdoNet.Salesforce
• Data.SqlClient

The first library will help fetch data from Salesforce, and the second has a SqlBulkCopy class, which enables bulk copying of data to SQL Server.

Now, Order data must be retrieved from Salesforce by using C1.AdoNet.Salesforce classes. These are similar to the well-known ADO.NET classes.

private void SalesforceToSqlServer()

{

  //Read OAuth credentials from config
  var config = this.config.Value;  
  string connectionString = string.Format("Username={0};Password={1};Security Token={2};OAuth Client Id={3}; OAuth Client Secret={4}; OAuth Token Endpoint={5}; Url={6}; Use Pool = false; Max Page Size = 200; Use cache = false;",

  config.Username, config.Password, config.SecurityToken, config.ClientId, config.ClientSecret, config.OAuthTokenEndpoint, config.Url); using (C1SalesforceConnection con = new C1SalesforceConnection(connectionString))  
{

  con.Open();

  var cmd = con.CreateCommand();

  //Query to select data from salesforce server.

  cmd.CommandText = "Select BillingStreet,BillingCity,BillingState,BillingCountry,BillingPostalCode,EffectiveDate,TotalAmount from [Order]"; var reader = cmd.ExecuteReader();  

  //Copy data to SQL Server OrderReport table

// The GetConnectionString() function gets the SQL Server connection string 
  SqlBulkCopy bc = new SqlBulkCopy(GetConnectionString());  
    bc.DestinationTableName = "OrderReport"; 
    try  
    {
      bc.WriteToServer(reader);
    }

    catch (Exception ex)

    {

      //log error

    }

  }

}

Updates to SQL Server

The example could be made into service to periodically update SQL Server with Salesforce data by changing the query to an insert query, adding a timestamp parameter “LastModifiedDate” in the query, and saving it to a config. The service will keep fetching data, which are added after the timestamp, and update the LastModifiedDate to config after every insert. There should also be additional consideration when day's request or data limits are exceeded, and further requests should be done after the stipulated time.

Download the Desktop DataConnector Explorer to see it in action.