How to Integrate Salesforce with External Database?

How to Integrate Salesforce with External Database?

Salesforce in itself is a very robust platform. It has all the tables that you may need and you can easily create new tables as per your requirements. Also if you want you can import an external table easily into the platform. However if you want to connect your salesforce solution to an external database, there is no inbuilt option for Salesforce integration. This is one of the drawbacks of the product but this also can be easily rectified.

In this post we are going to discuss two most used solutions for integration of salesforce with external database.

  • Integration of salesforce with external databases using custom methods built upon Data Loader tools
  • Using third party database integration service

Both these methods are easy to implement and are reliable.

Solution One : Salesforce Database Integration using Data Loader

Data Loader is a prebuilt tool that you can use to get your data into Salesforce objects or extract data form database objects into a desired destination. You can also use the tool to perform deletions in bulk using the ID fields of the data that you wish to delete. Data loader is an easy to use graphical tool that complements the web-based import wizard of the Saleforce product.
Since Data Loader tool required Force.com API, your Salesforce version must allow the use of APIs. Only Enterprise, Unlimited, Developer and Database edition allows use of API’s. You can download the tool by going to the Setup menu under Administer heading Data Management.
As we said, Data Loader complements imports wizard, therefore it is not always necessary to use the tool. Here are a few guidelines mentioning when you need to use the tool instead of the import wizard.

Use Data Loader when:

  • You need automated or scheduled data loads instead of a onetime load.
  • Data loader can be used to load 50,000 to 5,000,000 records. Below which it becomes very inefficient. If you need to load more than 5 million records, we recommend that you work with a certified Salesforce partner as they may help you in getting a more efficient solution.
  • The import wizard does not support the object that you wish to load.
  • You need to regularly export data for backups.

Use the import wizards when:

  • Data you want to load contains less than 50,000 records.
  • The imports wizard supports the object you want to load. To check whether the import wizards support the object or not, go to Setup, then Data Management.

Features of the Data Loader

The Data Loader has many important feature that makes it attractive to developers, such as

  • User-friendly wizard interface and a separate command line interface.
  • A dedicated batch mode interface with database connectivity .
  • Ability to support large files ranging millions of rows.
  • Supports for all inbuilt and custom objects.
  • A built in Comma Separated Value(CSV) file viewer.
  • Supports Windows 7 to Windows XP, along with a version that supports OS X.

Installing Data Loader

System Requirements

To use Data Loader, you need:

  • Microsoft® Windows® 7 or Windows XP
  • 120 MB free disk space
  • 256 MB available memory
  • Java JRE 1.6 or later (Windows 7 or Windows XP)
  • Sun JVM 1.6 or later (Windows 7 or Windows XP)
  • Administrator privileges on the machine

Installing Data Loader

  • In the application, go to Setup, then click Data Management > Data Loader
  • Click Download the Data loader, save the installer file, and click Run if prompted. If prompted by the OS to allow the program to make changes to the computer, click Yes.
  • If not prompted, double click the downloaded file to run the install wizard.
  • Run through all the installation procedures and then click finsh.
  • You can start the Data Loader through the Data Loader icon on the desktop or goto Start> All programs> Salesforce.com> Apex Data Loader.

Configuring Data Loader

You can use the settings menu to change and configure the default settings of the Data Loader. Here’s how you can change the main settings of the tool.

  • Open Data Loader by clicking Start > All Programs > Salesforce.com > Data Loder.
  • Open Settings.
  • Edit the settings fields as required. There are a wide variety of fields and you can use these to fine tune your configuration. You can know more about these fields in the Data Loader User guide that you can download here.
  • Click OK and save your settings.

Using the Data Loader tool, you can perform various operations which include exporting data, defining field mappings, inserting, updating, and deleting data, performing mass updates and mass deletes, uploading attachments and content, and reviewing output files. But it require good coding logic from both server and client ends. Also Data Loader only excepts a specific type of file therefore for every import or export you have to create a separate program that makes your database file compatible with the too, thus significantly increasing the development time. But this results in higher customization ability and better control, features that are inherent of all custom programs.
To know more about using Data Loader refer to the Data Loader User Guide

Solution Two : Using database integration service

An integration web service makes the job of connecting to an external database much easier than the Data Loader option. Using he service you can perform the task without having to study SalesForce.com APIs and writing, debugging, and maintaining custom code.
Web services also works on the principle of Apex callouts. They enable your backend data and make data retrieving calls through apex. This process also can be performed in two ways.

  • If your web service provides WSDL facility, you can use it to import data directly to Apex and Apex will auto-generate the apex classes. That means you won’t have to create XML files or HTTP request files. WSDL creates them both.
  • Web service can create service call messages using HTTP RESTful apex classes. But this means that you may have to manually configure the files for proper integration.

These web service can work only after you have established a connection to the both sources, database and Salesforce. That means that the service should have access to data tables, data structures, data types, and data records. Most modern web-services come with their own visual tools that make the process of connecting without you having to write a single line of code. They are equipped with a drag-and-drop job designer that you can use to create database connectors. You also have to provide salesforce.com authentication details and provide the details to rest of the databases.
These pre-built connectors, and rich drag-and-drop designer make integration of Salesforce and other complex endpoints a simple exercise that reduces the development time to mere days as opposed to months that are taken in traditional data integration services. Also most web services also comes with the option of connecting to multiple sources of databases so you can establish connection between Salesforce and on-premises apps, databases and files, or Salesforce integration with other cloud applications as well as social data sources.
Here’s a list of Salesforce integration services:
1. Informatica Cloud
2. Cast Iron
3. SnapLogic
4. Boomi
5. JitterBit
6. Sesame Relational Junction

One more Solution

Even though when compared to Data Loader option, using a web service is much easier, database mapping can prove to quite difficult especially if you don’t have proper understanding behind the process. Also inexperienced handling results in lots of errors which are very difficult to debug. So we recommend using an experienced and certified Salesforce integration consultant and developer for all your salesforce integration services.

The following two tabs change content below.
Pratyush Kumar

Pratyush Kumar

Co-Founder & Director at Algoworks, Open-Source | Salesforce | ECM
Pratyush is Co-Founder and Director at Algoworks. He is responsible for managing, growing open source technologies team and has spearheaded more than 200 projects in Salesforce CRM alone. He provides consulting and advisory to clients looking for services relating to CRM(Customer Relationship Management) and ECM(Enterprise Content Management). In the past, Pratyush has held consulting roles with various global technology leaders, such as Globallogic & HCL in India. He holds an Engineering graduate degree from Indian Institute of Technology, Roorkee.
Pratyush Kumar

Latest posts by Pratyush Kumar (see all)

Pratyush KumarHow to Integrate Salesforce with External Database?
  • Marci Brown

    I’m using Jitterbit for an integration. Works great and has a very nice UI!

  • http://simplysfdc.blogspot.com/ Johan

    I think you skipped the Outbound Message for sending data to other system and using SOAP API to push and pull data with Salesforce platform.