Salesforce is a cloud-based customer relationship management software that enables organizations to have a full-fledged CRM experience without having the trouble of hosting and managing servers. Salesforce is offered based on a software-as-a-service model. It can handle all aspects of customer relationship management including product ordering, accounting, invoicing and customer support. Even though Salesforce provides its own set of analytics and reporting suite, many cases organizations will require Salesforce data to be pulled to a separate database or data warehouse. This is because most organizations will have data coming in from a variety of sources and they would need to have all the data at one place for meaningful analysis. One of the typical requirements in this space is to have Salesforce data loaded into a relational database for analysis. In this post, we will deal with methods to load data from Salesforce to MySQL, one of the most popular relational databases.
Two approaches to move data from Salesforce to MySQL
There are two broad approaches one could take while loading data from Salesforce to MySQL:
Method 1: Writing custom code to move data from Salesforce to MySQL
Method 2: Implementing a Fully-Managed Data Integration Solution, Hevo Data
This blog will cover Method 1 in detail. Additionally, it will also highlight the limitations and challenges of this approach. Towards the end, you will also find details on Method 2 – so that you can weigh both options and decide on a way forward.
Loading Data from Salesforce to MySQL using Custom Code
Interacting with Salesforce
Salesforce provides different types of APIs and utilities to query the data available in the form of Salesforce objects. An overview of these APIs is as follows:
Salesforce REST APIs
Salesforce REST APIs provide a simple and convenient set of web services to interact with Salesforce objects. These APIs are recommended for implementing mobile and web applications that work with Salesforce objects.
Salesforce SOAP APIs
Salesforce SOAP APIs are to be used when the applications need a stateful API or have strict requirements on transactional reliability. It allows you to establish formal contracts of API behaviour through the use of WSDL.
Salesforce BULK APIs
Salesforce BULK APIs are tailor-made for handling a large amount of data and has the ability to download Salesforce data as CSV files. It can handle data ranging from a few thousand records to millions of records. It works asynchronously and is batched. Background operation is also possible with Bulk APIs.
Salesforce Data Loader
Salesforce also provides a Data Loader utility with export functionality. Data Loader is capable of selecting required attributes from objects and then export them to CSV file. It comes with some limitations based on the Salesforce subscription plan to which the user belongs to. Internally, Data Loader works based on bulk APIs.
Loading data from Salesforce to MYSQL – Step by Step
Login to Salesforce using the SOAP API and get the session id. For logging in first create an XML file named login.txt in the below format.
<?xml version="1.0" encoding="utf-8" ?> <env:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"> <env:Body> <n1:login xmlns:n1="urn:partner.soap.sforce.com"> <n1:username>your_username</n1:username> <n1:password>your_password</n1:password> </n1:login> </env:Body> </env:Envelope>
Execute the below command to login
curl https://login.Salesforce.com/services/Soap/u/47.0 -H "Content-Type: text/xml; charset=UTF-8" -H "SOAPAction: login" -d @login.txt
From the result XML, note the session id. This session id is to be used for all subsequent requests.
Create a BULK API job. For doing this, create a text file in the folder named job.txt with the following content.
<?xml version="1.0" encoding="UTF-8"?> <jobInfo xmlns="http://www.force.com/2009/06/asyncapi/dataload"> <operation>insert</operation> <object>Contact</object> <contentType>CSV</contentType> </jobInfo>
Please note that the object attribute in the above XML should correspond to the object for which data is to be loaded. Here we are pulling data from the object called Contact.
Execute the below command after creating the job.txt
curl https://instance.Salesforce.com/services/async/47.0/job -H "X-SFDC-Session: sessionId" -H "Content-Type: application/xml; charset=UTF-8" -d @job.txt
From the result, note the job id. This job-id will be used to form the URL for subsequent requests. Please note the URL will change according to the URL of the user’s Salesforce organization.
Use CURL again to execute the SQL query and retrieve results.
curl https://instance_name—api.Salesforce.com/services/async/APIversion/job/jobid/batch -H "X-SFDC-Session: sessionId" -H "Content-Type: text/csv; SELECT name,desc from Contact
Close the job. For doing this, create a file called close.txt with the below entry.
<?xml version="1.0" encoding="UTF-8"?> <jobInfo xmlns="http://www.force.com/2009/06/asyncapi/dataload"> <state>Closed</state> </jobInfo>
Execute the below command after creating the file to close the job.
curl https://instance.Salesforce.com/services/async/47.0/job/jobId -H "X-SFDC-Session: sessionId" -H "Content-Type: application/xml; charset=UTF-8" -d @close_job.txt
Retrieve the results id for accessing the URL for results. Execute the below command.
curl -H "X-SFDC-Session: sessionId" https://instance.Salesforce.com/services/async/47.0/job/jobId/batch/batchId/result
Retrieve the actual results using the result ID fetched from the above step.
curl -H "X-SFDC-Session: sessionId" https://instance.Salesforce.com/services/async/47.0/job/jobId/batch/batchId/result/resultId
This will provide a CSV file with rows of data. Save the CSV file as contacts.csv.
Load data to MySQL using the LOAD DATA INFILE command. Assuming the table is already created this can be done by executing the below command.
LOAD DATA INFILE'contacts.csv' INTO TABLE contacts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Alternately, instead of using the bulk API manually, Salesforce Data Loader utility can be used to export CSV files of objects. The caveat here is that usage of certain Data Loader functionalities are restricted based on the user’s subscription plan. There is also a limit to the frequency in which data loader export operations can be performed or scheduled.
Limitations of Custom Code Approach to Move Data from Salesforce to MySQL
As evident from the above steps, loading data from Salesforce to MySQL through the manual method is both a tedious and fragile process with multiple error-prone steps.
This works well when you have on-time or a batch need to bring data from Salesforce. In case you need data more frequently or in real-time, you would need to build additional processes to successfully achieve this.
A Simpler Alternative to Load Data from Salesforce to MySQL
A more graceful method to achieve the same outcome would be to use a code-free Data Integration Platform like Hevo Data. Hevo can mask all the ETL complexities and ensure that your data is securely moved to MySQL in just a few minutes. You can sign up for a 14-day free trial to experience the simplicity yourself.
Hevo can move data from Salesforce to MySQL in just 3 simple steps:
- Authenticate and configure your Salesforce data source
- Configure your MySQL destination where the data needs to be loaded
Hevo’s point and click interface gives you a hassle-free means to easily replicate your Salesforce data to MySQL. Without any coding and maintenance, all your Salesforce data will be ready for analysis within minutes.
Additionally, Hevo can also help you move data from 100s of different data sources, making your data integration infrastructure, future-proof.
What are your thoughts on the two approaches to move data from Salesforce to MySQL? Let us know in the comments.