Connect SQL Server Integration Services to Salesforce

In this section, you’ll find the detailed guide on how to connect SQL Server Integration Services and Salesforce. The solution described in this guide demonstrates how to establish a connection to Salesforce in Visual Studio using the SQL Server Integration Services extension. Additionally, you’ll explore two ways to obtain Salesforce data.

Prerequisites

  • Download and install the SQL Server Integration Services Projects 2022 extension.
  • Download and install Devart ODBC Driver for Salesforce.

Create a DSN for Salesforce

Before you start, you need to configure a DSN for Salesforce in the ODBC Data Source Administrator. It’s required to establish a connection to an ODBC database in Visual Studio. You can find the instructions for your OS in the following topics:

  • Configure a Windows DSN for Salesforce.
  • Create a DSN in the iODBC Administrator.
  • Configure an ODBC Driver Manager on Linux.

Configure the ODBC connection

1. To create an integration services project, open Visual Studio and click Create a new project.

Create a new project

2. Select Integration Services Project and click Next.

Integration Services Project

3. Name your project, specify its location, and click Create.

Configure the project

Optionally, if you want the project and the solution to be stored in different directories, clear Place solution and project in the same directory, enter the desired name into Solution name, and click Next.

4. Navigate to the Data Flow tab and click No data flow tasks have been added to this package. Click here to add a new Data Flow task.

Add a data flow task

5. To add a source for extracting data from an ODBC-compliant data source, drag the ODBC Source component from the SSIS Toolbox into the design area.

Add ODBC Source

6. Double-click the added source and click New.

Add a DSN

7. In the Configure ODBC Connection Manager dialog that opens, click New.

Click the New button

8. Select the previously configured DSN from the Use user or system data source name dropdown list, enter your credentials, and click OK.

Select the required DSN

Optional: To verify the connection, click Test Connection.

9. You should see that the data connection has been added. To confirm the properties, click OK.

Added data connection

Load data

To retrieve data, select the table from Name of the table or the view and click Preview.

Preview data

Check the obtained data.

Retrieved data

You can also retrieve data using a SQL query. To do this, select SQL Command from the Data access mode dropdown, enter the SQL statement in the SQL command text field, and click Preview.

Get data using a SQL query

The data is displayed in a table.

Retreived data