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.
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:
- The name of the linked server (in your case it should be ‘SALESFORCE’. You created this in the previous step).
- Three periods
- The Object / Table Name
An example query would be:
SELECT AccountNumber, Name
ORDER BY NAME ASC
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.
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.
What’s the query to get the 150 tables that records from sales-force in SQL server data base?
Hi Sarita, I would start with “Select * from SALESFORCE…sys_sfobjects”
The sys_sfobjects table contains information about the Salesforce.com objects. There is one row for each object in your organization. Some of the columns in this table are:
Name – Name of object
Createable – Is object createable ?
Deletable – Is object deletable ?
URLDetail – URL Detail for this object
URLNew – URL New for this object
Other Useful queries are:
— Select * from SALESFORCE…sys_sffields
— Select * from SALESFORCE…sys_sfpicklists
I hope this answered your question…if not please feel free to reach back out. Thanks!
Thank you for your article.
What about others ETL solution such as Skyvia (https://skyvia.com/) or Informatica (https://www.informatica.com/), are they better than DBAmp or it still rules the market (again on my op.)
Hello Osvald, thanks for reaching out! The tools you mentioned are primarily used for ETL purposes rather than for Live Queries. DBAmp is a tool that is directly integrated with Microsoft SQL Server and is used to make Live Queries on SalesForce Data. It is not really an ETL tool, but rather is sort of an enhancement to SQL. DBAmp will give you the ability to query Salesforce data using SQL syntax. You can certainly point any ETL tool to use DBAmp, which is really useful.
Have you used Informatica and Skyvia to pull Salesforce data for ETL purposes?
Hi Jeff! We are successfully using Skyvia to pull Salesforce data to SQL Server on a daily basis. By the way this tool has a query builder embedded which allows to query Salesforce data using SQL syntax – https://skyvia.com/query/
Thanks for a great article! It is one of the most demanded tutorials!
You’re welcome! Thank you for your comment! Glad you found this useful.