5 Ways to Connect Salesforce to SQL Server

Connecting data from Salesforce to SQL Server contributes to more accurate data analysis and efficient reporting. Moving data in another direction helps companies enrich their customers’ profiles. However, SQL Server to Salesforce integration has always been a challenging task.

In this article, we describe 5 different methods to connect Salesforce and SQL Server with ease and no coding: 

1. Import and Export Wizard
2. ODBC Drivers
3. SSIS Data Flow
4. Skyvia Data Integration
5. Skyvia Connect

Table of Contents

1. Salesforce to SQL Server Integration: Import and Export Wizards
2. Salesforce to SQL Server Integration: ODBC Driver and Linked Server
3. Salesforce to SQL Server Connection: SSIS Data Flow
4. Code-free Salesforce and SQL Server Integration: Skyvia Data Integration
5. Real-time Salesforce and SQL Server Connection: Skyvia and Salesforce Connect
6. Use Cases for Salesforce to SQL Server Integration
7. Conclusion
8. FAQ

In our article, we will pay close attention to methods 4 and 5 and describe in detail all nuances of data migration when using these methods.

Salesforce to SQL Server Integration: Import and Export Wizards

SQL Server has a whole ecosystem of native and third-party tools for importing data from other sources. Let’s start by exploring SQL Server Import and Export Wizards, standard tools for data movement.

SQL Server Import Wizard can extract data from different data sources:

• SQL Server databases
• Oracle
• Flat files like CSV
• PostgreSQL
• MySQL
• Azure Blob Storage.

Importing data directly from Salesforce requires a third-party ADO.NET provider or an ODBC driver. However, there’s a workaround involving a CSV file containing Salesforce data.

There are several Salesforce tools for exporting Salesforce data to CSV files, both in the cloud, like Skyvia Data Loader or Dataloader.io, and locally installed, like the Salesforce Data Loader tool.

We’ll explore the data import case using the Data Loader tool.

1. In your Salesforce account, go to Setup.
2. Under Platform Tools, click Data Management and select Data Loader.

3. Download and install an appropriate version of Data Loader.
4. In your Salesforce account, select the necessary data objects to be exploited into a CSV file.
5. Use SQL Server Import Wizard to upload Salesforce data into SQL Server tables. Check more information on SQL Server Import Wizard in Microsoft documentation.

NOTE: Please note that this integration method is unidirectional. You cannot migrate data in the reverse direction.

Benefits	Limitations
– Built-in tool, no need for extra downloads and installations. – Easy to use with Microsoft-typical interfaces. – Compatible with other SQL databases and flat files.	– No direct connection to Salesforce. – No way to compare lost or modified data. – Data export limitations on data restoration, deduplication, etc.

Salesforce to SQL Server Integration: ODBC Driver and Linked Server

Linked servers allow SQL Server to read data from external sources and execute SQL commands against them. You can link another data source to SQL Server via OLE DB or ODBC interface.

There are quite a few ODBC drivers for Salesforce on the market. Consider using Devart ODBC Driver for Salesforce, which is available for all popular operating systems: Linux, Windows, and macOS.

Below, find a short tutorial on how to create a linked server to Salesforce in SQL Server Management Studio on Windows OS.

1. Download ODBC Driver for Salesforce, double-click on the .exe file, and follow the installation instructions.
2. Go to System and Security > Administrative Tools > ODBC Data Sources to open the ODBC Data Source Administrator tool.
3. Select the System DSN tab and click Add to create a new data source.
4. Select Devart ODBC Driver for Salesforce from the list of available drivers and click Finish.
5. Indicate the name and Salesforce account together with credentials and other necessary information about the source.
6. In the ODBC Data Source Administrator tool, click Test to test the connection with Salesforce.
7. Once the connection is successfully established, you can retrieve data from Salesforce to SQL Server. It’s also possible to perform a range of data management tasks with INSERT, UPDATE, and DELETE operations.

NOTE: This method is also for unidirectional integration from CRM to database.

Benefits	Limitations
– Can access external data from outside of SQL Server. – Optimizes query performance by pushing processing to the remote data source rather than bringing all data into your local server. – Addresses diverse data sources similarly.	– Performance overhead, which might significantly reduce runtime. – Running queries against a remote server can lead to difficulties in improving query performance.

Salesforce to SQL Server Connection: SSIS Data Flow

SQL Server Integration Services (SSIS) is a powerful data integration solution designed for a broad range of data migration tasks. If you need more than a simple export of Salesforce data to SQL Server, SSIS could be a good option for that. You can perform data transformation and build complex integration scenarios with this tool.

To load data from Salesforce, you will also need third-party SSIS components, such as Devart SSIS Data Flow Components for Salesforce. This solution helps to synchronize Salesforce with SQL Server, migrate data from/to Salesforce, and automate integration via SSIS Data Flow tasks.

To connect Salesforce and SQL Server using SSIS:

1. Download Devart SSIS Data Flow Components for Salesforce.
2. Proceed with on-screen instructions to install and configure this solution.
3. In SSIS, create an Integration Package.
4. Create a Data Flow task.
5. Add a Devart Salesforce Source component on the diagram and configure it thoroughly to get the necessary Salesforce data.
6. Add components to a diagram, linking them and configuring column mapping. If needed, add the respective transformation components and connect them with links. You can use the standard ADO.NET Destination component with an ADO.NET connection to load data to SQL Server.

Similarly, you can load data in the reverse direction or configure bi-directional data flow with the SQL Server Integration Services.

Benefits	Limitations
– Can handle data from heterogeneous sources. – Provides powerful transformations. – Data can be loaded to many different destinations simultaneously.	– Designed with tech professionals in mind, it might be difficult for non-developers. – Associated with on-premises limitations since it needs porting for cloud or hybrid setups. – It functions 100% only in the Microsoft environment but less so with other SaaS apps.

Code-free Salesforce and SQL Server Integration: Skyvia Data Integration

Skyvia offers several products (ways) to integrate Salesforce and SQL Server data. In this section, we will start with the Skyvia Data Integration product for building ETL and ELT pipelines. In the next section, will go on with the Skyvia Connect (Web API Server), which works in conjunction with the Salesforce Connect through OData protocol. These methods don’t require any coding skills and can be easily implemented by businesses of different sizes, including SMBs and large Enterprises from Fortune 500.

Skyvia Data Integration is a great option for loading data from one source to another in any direction. You can easily transfer data from Salesforce to SQL Server and vice versa by connecting to these systems from the Skyvia interface and setting up integrations. Skyvia is fully managed, so minimal setup is required. For convenience and better understanding, we describe use cases with the Skyvia Import (ETL) and Skyvia Replication (ELT) tools below.

Salesforce to SQL Server Integration with Data Import (ETL)

The Import tool is an ETL-based solution with a visual wizard that allows users to load data from SQL Server to Salesforce and in the opposite direction. It also allows the application of various data transformations and mapping to match the data structures of two systems.

In the sample integration scenario provided below, we copy data from the Customers table on SQL Server and send it to Accounts and Contacts on Salesforce. The main challenge of such an operation is to preserve relations of the SQL Server data when importing them to Salesforce. On data import, the relation between the corresponding Account and Contact must be created in the Salesforce database. This is easy with Skyvia. When data is inserted from one table into multiple Salesforce objects, Skyvia builds such relations automatically.

To connect SQL Server to Salesforce, there are several essential steps to take:

1. Create connections for both tools in Skyvia.
2. Create an Import package to migrate data.
3. Schedule recurring data updates.
4. Run integration and check the results.

1. CREATE A CONNECTION TO SQL SERVER AND SALESFORCE

1. Click + Create New in the top menu and select Connection in the menu on the left.

2. Select the Database category from the drop-down list on the left and choose SQL Server from available databases.

3. Select Connection Mode (Direct, Agent, or Agent with Alias) and specify other required parameters according to the selected mode. To learn more about SQL Server connection setup, visit this page.

4. Create a new connection to Salesforce in Skyvia by clicking + Create New in the top menu and selecting Connection in the menu on the left.
5. Select the CRM category from the drop-down list on the left and choose SQL Server from available databases.
6. To connect to Salesforce, use either Salesforce User Name and Password or OAuth authentication. If you don’t want to store your Salesforce credentials in Skyvia, we recommend trying the OAuth method. After clicking Sign in with Salesforce, the Salesforce login window pops up. You sign in via a web browser and automatically receive an access token. After that, you save the connection.

When two connections are established, let’s proceed to the creation of the integration scenarios to quickly and conveniently export data from SQL Server to Salesforce or vice versa.

2. CREATE AN IMPORT PACKAGE TO MIGRATE DATA

1. In the top menu, click +Create New and select Import in the Integration column.

2. In the opened package editor, select Database or cloud app source type. In the Connection drop-down list, select SQL Server as a source and Salesforce as a target. 
3. Create a new integration task by clicking Add new.

NOTE: A task is a unit of an ETL process (data extraction, transformation, and loading). When creating an import package, we need to add a task for each SQL Server table.

4. In the task editor, select data from SQL Server tables, set filters, if needed, and select the DML operation (Skyvia supports not only INSERT operation for data import but also UPSERT, UPDATE, and DELETE).

5. Configure mapping settings. Skyvia offers numerous mapping types, including Column, Expression, Lookup (Source and Target Lookup), Constant, Relation, External ID, and many others.
   
   In our case, we need to map the Customers table columns (SQL Server) to the target Account and Contact object fields (Salesforce). Some columns of Customers table, such as Phone and Fax, are mapped automatically to Account object fields. For others, we use simple column mapping, where each field of the source table is mapped to the corresponding target object field. We map the CompanyName column to Name, the Address column to BillingStreet, the City column to BillingCity, etc.
   
   To map Contact object fields, click the target table name (Account) and select Account.Contact from the drop-down list in the upper right corner. Below, you can see what the mapping process looks like.

6. Save the task.
7. Click Create to put your import task for execution.
8. Finally, click Run to start package execution.

3. SCHEDULE RECURRING DATA UPDATES

Skyvia allows you to set a schedule to execute import automatically. This might be useful for configuring data loading operations to run periodically or delay an operation to a later time.

If automated data transfer is needed, click Schedule and set the time preferences for the import task run.

4. RUN INTEGRATION AND CHECK THE IMPORT RESULTS

Click Run to start the integration. You can check the results of the import integration by downloading an Excel file that is available in the Monitor tab. Find out information about successful and failed records in detail. Import is considered successful if all the records were successfully loaded to a destination and failed either when at least one record has not been loaded successfully or when the integration has not been executed completely (for example, when its connection became invalid).

Load Salesforce Data to SQL Server with Data Replication (ELT)

With the Replication tool, you can copy data from cloud applications to databases. You don’t need to create tables in the database yourself since Skyvia can automatically do that itself.

Note that replication doesn’t allow any data transformations. It’s also unidirectional, which means you can only replicate data from Salesforce to SQL Server.

To replicate data from Salesforce to SQL Server, you will need to:

1. Create connections to both tools in Skyvia.
2. Create the replication package.
3. Schedule recurring data updates.
4. Run replication and check the results.

1. CREATE CONNECTIONS TO SALESFORCE AND SQL SERVER

If you haven’t connected these tools yet, follow the procedure mentioned previously in this article. Otherwise, proceed to the next step.

2. CREATE THE REPLICATION PACKAGE

1. Click +Create New in the top menu and select Replication under the Integration column.
2. Select Salesforce as a source and SQL Server as a target.
3. As soon as you select Salesforce, the table with Salesforce objects will appear on the right. Choose the data objects from which you want to replicate data. For each of the selected objects, you can easily edit and filter its data according to specified conditions to replicate them correspondingly to the SQL Server database.

4. You can also select the Incremental Updates option if needed. That way, Skyvia doesn’t perform a full replication (copying of all the data) each time the replication is executed. Instead, it performs a full replication only on the first run. During subsequent runs, Skyvia detects data that was changed in Salesforce and applies these changes to the SQL Server database.
5. Click Create to put your replication for execution.
6. Finally, click Run to start the data transfer.

3. SCHEDULE RECURRING DATA UPDATES

Skyvia allows you to set a schedule for regular replication with incremental updates. Click Schedule and set the time preferences for the replication task run.

4. RUN INTEGRATION AND CHECK THE REPLICATION RESULTS

Click Run to start the integration. You can check the results of the replication integration by downloading a file that is available in the Monitor tab. Find out information about successful and failed records in detail. Replication is considered successful if all the records were successfully loaded to a destination and failed either when at least one record has not been loaded successfully or when the replication has not been executed completely (for example, when its connection became invalid).

Benefits	Limitations
– Skyvia is fully cloud-based, easy to set up, and requires no coding skills. – Skyvia is among the top user-friendly data integration tools. – Skyvia connects to 200+ data sources. – Skyvia can be used for free.	– Limitations on data amounts transferred according to the pricing plans.

Real-time Salesforce and SQL Server Connection: Skyvia and Salesforce Connect

Now, let’s explore Skyvia Connect (Web API Server), which works in conjunction with Salesforce Connect through OData protocol. 

Skyvia Connect is a great option for displaying only necessary data from SQL Server to Salesforce by request. Using this product, you can create an SQL Server OData endpoint and link SQL Server data to Salesforce via Salesforce Connect.

Salesforce Connect is similar to SQL Server linked servers.

• Linked servers allow you to work with external data from SQL Server, like with its own database.
• Salesforce Connect allows you to work with external data from Salesforce as with Salesforce’s own objects.

There are many ways to create an OData endpoint for SQL Server. Most involve developing a service, caring about security, creating hosting and domain, obtaining respective certificates, deploying, administering, and maintaining. As a result, you need to take many preliminary steps to get an endpoint available from the Internet.

With Skyvia, you don’t need to build a web API manually but just take a few simple steps to connect Salesforce to SQL Server:

• Create a connection to SQL Server (which you want to publish data from).
• Create OData endpoint to SQL Server.
• Link SQL Server data to Salesforce via Salesforce Connect (Salesforce Lightning).

You don’t need to worry about hosting, deployment, or administration since Skyvia helps you to automate the process. You can create both public and private endpoints and optionally limit IP addresses, from which the data of the endpoint can be accessed. This feature is foreseen in the endpoint security settings.

STEP 1. CREATE SQL SERVER CONNECTION IN SKYVIA

If you haven’t created a connection to the SQL Server from Skyvia yet, follow the procedure mentioned in this article. Otherwise, proceed to the next step.

STEP 2. CREATE ODATA ENDPOINT TO SQL SERVER

1. Log into your account.
2. Click + Create New in the top menu and select OData Endpoint under the Connect column.

NOTE: Skyvia offers two endpoint editor modes: simple and advanced. The advanced mode allows you to visually design your OData endpoint, adjusting entities and customizing associations between entity types. As an example, we select an advanced mode in the OData Endpoint Wizard.

3. Select SQL Server connection if you have created it earlier in Skyvia. If not, create it by clicking the +Create New button on the right and specify the requested SQL Server parameters in the opened window.

4. Define which data to publish via the endpoint. You can add SQL Server tables to the endpoint by dragging them from the list to the diagram, and Skyvia will automatically create the corresponding entity set and entity type. Skyvia also automatically creates relationships (associations) with other entity types on the diagram. If necessary, you can edit or delete the generated relationships or even create your custom ones.
   
   You can freely configure the OData entities in your endpoint by modifying the generated names of entity types, their properties, and entity sets. Additionally, you can exclude data source columns from entities so that they are not available via the endpoint.

5. You can optionally change endpoint security settings. Namely, you can add user accounts with passwords to make your endpoint data available only for authenticated users. Additionally, you can enable access to your endpoint only for the specific IP addresses.
6. Specify the new endpoint name and configure additional settings, such as an OData protocol version and endpoint access mode. Skyvia Connect supports OData v1-v3 (ATOM format used for returned data and metadata) and OData v4 (JSON format used for returned data and metadata).

7. After the endpoint has been created, copy its URL on the Overview tab to use it in Salesforce Connect.

STEP 3: LINK SQL SERVER DATA TO SALESFORCE VIA SALESFORCE CONNECT (SALESFORCE LIGHTNING)

1. Sign into your Salesforce account and click Setup.
2. In the menu on the left, under Platform Tools, click Integrations and then select External Data Sources.
3. In the opened window:

• Specify your OData endpoint name.
• Select the OData version.
• Enter your endpoint URL copied from Skyvia (Step 7).
• Select the corresponding checkbox if you use an endpoint to a writable data source.
• Configure authentication settings if you created user accounts with passwords for your endpoint in Skyvia.

NOTE: With Salesforce Connect (Salesforce Lightning), you can link SQL Server data obtained via the OData protocol in Skyvia to Salesforce as external objects and then work with these data as with usual SFDC objects.

4. Select the exposed tables you want to synchronize. This will create the necessary external objects automatically.
5. After defining external data source and external objects, you may add tabs for external objects in order to work with them via Salesforce UI.

Benefits	Limitations
– No need to build a web API manually. – Simplified process of OData endpoint creation.	– Requires technical expertise

To try Skyvia Connect yourself, register on our platform and receive a free plan automatically. You may also turn to our technical support for no-cost consultation.

Use Cases for Salesforce to SQL Server Integration

This integration can be helpful in the following cases:

• Advanced analytics and reporting. If a company uses an SQL Server database, importing Salesforce data there will boost advanced analytics and reporting. This is possible thanks to SQL Server Reporting Services (SSRS) and its powerful querying capabilities, which allow you to combine data from other enterprise resources for comprehensive reporting.
• 360-degree customer view. If a company needs to get a complete overview of customer profiles, transfer data from SQL Server to Salesforce to enrich client data. That way, sales and support teams gain full visibility into customer journeys.
• Historical data analysis. Since Salesforce implies limits on historical data, sending it to an SQL Server is a good option for archiving.
• Data backups. The integration ensures that backups with business-critical data from Salesforce are safely stored on external resources. That way, important information is protected from accidental deletion. Regular backups go in line with disaster recovery requirements.
• Data Science and Machine Learning. Applying ML and data mining algorithms to Salesforce data stored on the SQL Server grants insights about your business development.

Conclusion

In this article, we have focused on the most effective and reliable methods for connecting SQL Server and Salesforce. If you need to load data in both directions, then Skyvia and SSIS would work well. While SSIS is for tech-wise specialists, Skyvia is accessible to non-technical users as well.

Skyvia Connect would be suitable for sending SQL Server data to Salesforce. While Import and Export Wizard and Linked Servers are designed for the integration in the opposite direction.

It’s up to you to decide which method best fits you!

FAQ