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:
Now, let's look at each step in detail:
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:
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.
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:
Here is the schema for above fields as per Salesforce:
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
)
Next, create a .NET Core console application and add the following packages from NuGet:
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
}
}
}
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.