By: Jeff Levy

Data Warehouse Developer @ Key2 Consulting

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.

 

Salesforce integration with SQL Server via DBAmp

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.

 

DBAmp installation

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.

 

DBAmp setting up linked server

 

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

 

DBAmp properties

 

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’.

 

SQL Server Management Studio (SSMS) Linked Server

 

Fill out the ‘General’ page as follows:

 

Salesforce general page

 

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.

 

Salesforce security page linked server

 

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

 

Salesforce server options page

 

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:

 

Linked server test connection

 

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.

 

Salesforce Objects map to SQL Server tables

 

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:

 

SQL Server Salesforce

 

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

 

Result set Salesforce 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

 

42 Shares
Share42
Share
+1
Tweet