How to Connect MSSQL Data to Salesforce

Published: August 28, 2024 - 9 min read

Frank Ferris
mssql data to salesforce

How to Connect MSSQL Data to Salesforce? A Detailed Step-by-Step Walkthrough

Looking to push your MSSQL data to Salesforce? This comprehensive guide will walk you through three powerful methods to connect MSSQL data to Salesforce, enabling seamless data synchronization and enhanced business intelligence.

By the end of this tutorial, you’ll be able to choose the best integration method for your needs and implement it with confidence.

Advantages of Connecting MSSQL Data to Salesforce

Before diving into the specific methods, let’s explore the key benefits of integrating MSSQL data with Salesforce:

  1. Unified Data View: By combining customer data from MSSQL with Salesforce CRM, you create a comprehensive 360-degree view of your customers. This holistic perspective enables better customer service, more targeted marketing efforts, and improved overall business strategies.
  2. Enhanced Reporting: Leveraging MSSQL’s robust data storage capabilities alongside Salesforce’s powerful reporting tools opens up new possibilities for deeper insights. You can create more complex reports and dashboards that draw from a wider range of data sources, leading to more accurate and actionable business intelligence.
  3. Automated Data Updates: Ensuring that Salesforce always has the most up-to-date information from your MSSQL database is crucial for maintaining data accuracy and relevance. Integration allows for automated synchronization, reducing manual data entry errors and saving valuable time.

Top 3 Methods to Connect MSSQL Data to Salesforce

Let’s explore three effective methods to integrate MSSQL with Salesforce:

SolutionBest For
CoefficientNon-technical users who want to import MSSQL data into Salesforce for reporting and analysis, leveraging Google Sheets or Excel as an intermediary for data transformation
Salesforce ConnectOrganizations needing real-time access to external MSSQL data within Salesforce without data replication
TalendCompanies that require advanced data transformations and data quality checks when moving data from MSSQL to Salesforce

Method 1: Coefficient

Image6

Coefficient is a powerful spreadsheet add-on that enables users to connect MSSQL data to Salesforce via Google Sheets or Excel. This method is ideal for teams seeking a user-friendly, no-code solution with robust data transformation capabilities.

Step-by-Step Walkthrough:

Step 1. Install Coefficient

For Google Sheets

  • Open a new or existing Google Sheet, navigate to the Extensions tab, and select Add-ons > Get add-ons.
  • In the Google Workspace Marketplace, search for “Coefficient.”
  • Follow the prompts to grant necessary permissions.
  • Launch Coefficient from Extensions > Coefficient > Launch.
  • Coefficient will open on the right-hand side of your spreadsheet.
Coefficient installation screen in Google Sheets with the “Install” button highlighted.

For Microsoft Excel

  • Open Excel from your desktop or in Office Online. Click ‘File’ > ‘Get Add-ins’ > ‘More Add-Ins.’
  • Type “Coefficient” in the search bar and click ‘Add.’
  • Follow the prompts in the pop-up to complete the installation.
  • Once finished, you will see a “Coefficient” tab in the top navigation bar. Click ‘Open Sidebar’ to launch Coefficient.
Coefficient installation screen in Microsoft Excel with the “Install” button highlighted.

Step 2. Connect and Import Data from MS SQL Server

  • Open Coefficient Sidebar: Click on the menu icon and select Connected Sources. Type “MS SQL” in the search bar and click Connect.
 Coefficient sidebar with “Connected Sources” selected, and “MS SQL” typed into the search bar, ready to connect.
  • Enter Credentials: Fill in the required fields (Host, Database name, Username, Password, and Port) .
  • Import Data: Click Import from… > MS SQL Server. Select From Tables & Columns, choose your schema and tables, and import the data into your spreadshseet.
Coefficient import screen showing the process of importing data from MS SQL Server into a spreadsheet.

Step 3. Push Your Data to Salesforce

  • Navigate to Export: In Coefficient’s menu, click Export to…, then select Salesforce from the list of available data sources .
  • Select Data and Action: Choose the tab and header rows in your spreadsheet that contain the data you want to export. Define your tab and header row. Select the Salesforce Object (e.g., “Opportunity”, “Contacts”), and choose the type of action—Update, Insert, Upsert, or Delete .
  • Map Fields: Map the fields from your spreadsheet to the corresponding Salesforce fields. Coefficient will automatically map these fields if the data originated from Salesforce .
Coefficient field mapping interface showing spreadsheet fields mapped to Salesforce fields.
  • Customize and Export: Specify additional settings like batch size, whether to export empty cells on an update, and the column for results. Confirm your settings, highlight the rows to export, and click Export .
  • Coefficient export settings screen with batch size, empty cell handling, and other options configured for Salesforce export.

Your spreadsheet will display updated records, along with their export status and the date/time stamp of the update.

Pros:

  • User-friendly interface, suitable for non-technical users
  • Powerful data transformation capabilities using familiar spreadsheet functions
  • Automatic data refresh and sync scheduling

Cons:

  • Requires Google Sheets or Excel as an intermediary
  • May not be suitable for extremely large datasets

For more information on Coefficient’s MySQL integration capabilities, click here.

Method 2: Salesforce Connect

Salesforce Connect is a native Salesforce feature that provides real-time access to external data sources, including MSSQL, without copying the data into Salesforce.

Step 1. Set up an OData producer

Install and configure an OData producer for your MSSQL database. This could be a third-party tool or a custom-built solution that exposes your MSSQL data as OData endpoints.

Step 2. Create a new External Data Source

In Salesforce Setup, navigate to “External Data Sources” and click “New External Data Source.” Select “Salesforce Connect: OData 4.0” as the type.

Step 3. Configure the External Data Source

Enter a name for your data source and the URL of your MSSQL OData endpoint. Set the authentication settings based on your OData producer’s requirements.

Step 4. Create External Objects  

In Salesforce Setup, go to “External Objects” and click “New External Object.” Map the external object to the corresponding MSSQL table, defining object name, fields, and relationships.

Step 5. Grant user permissions

Assign the necessary permissions to users who need to access the External Objects. This includes “Read” permissions on the External Data Source and specific object-level permissions.

Pros:

  • Real-time access to MSSQL data eliminates the need for data replication, ensuring users always see the most current information.
  • As a native Salesforce solution, it integrates seamlessly with other Salesforce features and security models.
  • By not storing external data in Salesforce, it reduces data storage costs and simplifies data management.

Cons:

  • The technical setup and maintenance of an OData producer may require specialized skills or additional resources.
  • Performance limitations may arise for large datasets or complex queries, as data is accessed in real-time from the external source.

Method 3: Talend

Talend interface showing setup for connecting and transferring data from MSSQL to Salesforce.

Talend is a comprehensive data integration platform offering robust ETL (Extract, Transform, Load) capabilities for connecting MSSQL to Salesforce.

Step 1. Install and set up

Talend Download and install Talend Open Studio or sign up for Talend Cloud. Launch the application and create a new project for your MSSQL to Salesforce integration.

Step 2. Create a new job

In your Talend project, create a new job for the MSSQL to Salesforce integration. This will be the workflow that defines your data movement and transformation.

Step 3. Configure MSSQL source

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Drag the “tMSSqlInput” component onto the job canvas. Double-click to configure it with your MSSQL server details, including host, port, database name, username, and password. Define your SQL query to extract the desired data.

Step 4. Add transformation components

If needed, add transformation components to the job. For example, use “tMap” to join data from multiple tables or “tFilterRow” to apply data quality rules.

Step 5. Set up Salesforce destination

Add the “tSalesforceOutput” component to your job. Configure it with your Salesforce credentials and select the target object (e.g., Account, Contact) for data insertion or update.

Step 6. Configure error handling and logging

Add error handling components like “tLogRow” to capture and log any issues during the integration process. This helps in troubleshooting and maintaining data integrity.

Step 7. Run and schedule the job

Test your job by running it manually. Once satisfied with the results, use Talend’s scheduling features to automate the integration process at your desired frequency.

Pros:

  • Advanced data transformation and cleansing capabilities allow for complex data manipulations during the integration process.
  • The platform is well-suited for handling complex integration scenarios involving multiple data sources and targets.
  • Talend supports both batch and real-time integration, providing flexibility for various use cases.

Cons:

  • The learning curve for Talend can be steeper compared to no-code solutions, requiring more time for users to become proficient.
  • For simple integration needs, Talend’s extensive feature set may be more than necessary, potentially adding complexity to the process.

Streamline Your MSSQL to Salesforce Integration with Coefficient

Connecting MSSQL data to Salesforce doesn’t have to be a daunting task. While each method has its strengths, Coefficient offers a user-friendly solution that strikes an ideal balance between ease of use and powerful functionality. By leveraging familiar tools like Google Sheets or Excel, you can quickly set up a robust integration between MSSQL and Salesforce, enabling better data-driven decision-making across your organization.

Ready to transform your data integration process? Try Coefficient today and experience the seamless connection between MSSQL and Salesforce for yourself.

Frequently Asked Questions

Q: How do I transfer data from SQL Server to Salesforce?

A: There are several methods to transfer data from SQL Server to Salesforce. One efficient way is to use Coefficient, which allows you to import SQL Server data into Google Sheets or Excel and then push it to Salesforce. Alternatively, you can use Salesforce’s Data Loader, third-party ETL tools, or Salesforce Connect for real-time access without data replication.

Q: Can you use SQL to query Salesforce?

A: While Salesforce doesn’t directly support SQL queries, you can use SOQL (Salesforce Object Query Language) which is similar to SQL. With Coefficient, you can import Salesforce data into Google Sheets or Excel and then use SQL-like functions to query and analyze the data.

Q: What are some free tools for SQL Server integration with Salesforce?

A: While many enterprise-grade solutions are paid, there are some free options available:

  • Salesforce Data Loader (free for Salesforce customers)
  • Talend Open Studio (open-source version)
  • Coefficient (offers a free tier with limited features)

Each tool has its own strengths, but Coefficient provides an easy-to-use interface and powerful features even in its free version.

Further Reading

For more insights on data integration and reporting, check out these related articles:

By leveraging the right tools and methods, you can unlock the full potential of your MSSQL data within Salesforce, driving better insights and more informed decision-making across your organization.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Frank Ferris Sr. Manager, Product Specialists
Frank is the spreadsheet ninja you never knew existed. Frank's focus throughout his career has been all about growing businesses quickly through both strategy and effective operations. His advanced skillset and understanding of how to leverage data analytics to automate processes and make better and faster decisions make him the unicorn any team can thrive with.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies