How to Query Salesforce Data Directly From SQL Server

By: Jeff Levy

 
 
 
If you’re a business intelligence (BI) and/or data warehouse practitioner, chances are you’ve handled sales data in the past or will do so sometime in the future.
 
Incorporating sales data into business intelligence processes has become a key ingredient for successful BI for companies across the globe.
 
As BI & data warehouse professionals, it’s important for us to understand how to work with the world’s leading CRM software and tools, and furthermore, how that software works best with BI and data warehousing tools.
 
Salesforce CRM is one of the most popular (if not the most popular) CRM platforms in the world. This article will show you how you can query data from a Salesforce instance directly from SQL Server.
 
SQL Server is compatible with a tool called DBAmp that makes integration seamless and easy.
 
Here are the requirements for DBAmp integration with SQL Server:

• Salesforce Enterprise Edition or higher
• SQL Server 2005 or higher
• Window 2008 R2 or higher
• 64 bit Windows OS
• .NET 4.5 library or higher

Step 1. Signing up for DB AMP Service

 
Go to http://www.forceamp.com/ and sign up for service or a 30 day Trial. If you sign up for the trial, make sure that you provide a corporate email address. After submitting the request, a representative will contact you and send the software (via zip file) and serial number through email.

Source: http://www.forceamp.com

Step 2. Installing DB Amp

 
Once you have received the zip file and serial number, log into the server where your SQL Server instance is located and copy the files over to that machine. It will not work if you try to install DBAmp from any other machine.
 
After moving the files to the server, you will want to execute the ‘DBAmpInstall’ application. The install wizard will ask you for your serial number so have it handy. The wizard should be able to map the DBAmp software to your server automatically.

Step 3. Configure DBAmp on SQL Server

After a successful installation of DBAmp, you should notice a ‘DBamp.DBAmp’ Linked Server Provider. Go to Server Objects → Linked Servers → Providers to find the Provider.

Right click the provider and select ‘Properties’. Next, enable the following options:

Step 4. Creating a Linked Server on SQL Server

An easy way to create a Linked Server Object is through SQL Server Management Studio (SSMS). Once logged on to SSMS, create a new Linked Server Object by Right Clicking on the ‘Linked Servers’ Folder and Select ‘New Linked Server’.

Fill out the ‘General’ page as follows:

Note:  For the Location field, if you are using a sandbox, enter https://test.salesforce.com. Otherwise leave it blank.

For the Security Page, please fill out the following fields:

• Local Login – This is your login to your SQL Server Instance. In this case, a Windows username is being used.
• Remote User – This is the username that you use to log into your Salesforce instance.
• Remote Password – This field is actually a concatenation of two things…your Salesforce password and your Salesforce security token.
  • For example, if your password is ‘IloveSQLServer’ and your security token is ‘WFETSMoraH67hMP3sVHew0Qc’, then you should enter ‘IloveSQLServerWFETSMoraH67hMP3sVHew0Qc’ for this field.
  • You will need to obtain your token from your Salesforce administrator.

For the ‘Server Options’ page, please mark the following options accordingly:

After selecting the appropriate options, press ‘OK’ to create the SALESFORCE linked server.
 

Step 5. Verifying the newly created Linked Server

 
The first thing you will want to do after creating the Linked Server is to test the connection. You can simply test the connection by right clicking your new Linked Server Connection and selecting ‘Test Connection’. The following message should come up:

After verifying the connection, expand the ‘Catalogs’ folder to find the ‘default’ database. Inside this database, you will find a list of SQL Server Tables that map directly to your Salesforce Objects.

In order to query your Salesforce instance, you will need to use the following three parts in the ‘FROM’ clause:

1. The name of the linked server (in your case it should be ‘SALESFORCE’. You created this in the previous step).
2. Three periods
3. The Object / Table Name

An example query would be:

Before executing the query you might encounter a red squiggly line. You can ignore this and hit execute. You should see a result set similar to this in SQL Server after executing:

You’ll notice that this result set is the same as the Salesforce user interface.

Summary

 
Salesforce provides many mechanisms to extract data from their platform. Other options such as their APIs (SOAP and REST Web services) are good but can take a long time to develop. For SQL Server developers, DB Amp provides a fast and easy way to connect to a Salesforce Instance without getting bogged down with tedious coding.
 

Sources:

http://www.forceamp.com