How to Access Live Salesforce Data in Power Automate Desktop via ODBC

Jerod Johnson
Director, Technology Evangelism

The CData ODBC Driver for Salesforce enables you to integrate Salesforce data into workflows built using Microsoft Power Automate Desktop.

The CData ODBC Driver for Salesforce enables you to access live Salesforce data in workflow automation tools like Power Automate. This article shows how to integrate Salesforce data into a simple workflow, moving Salesforce data into a CSV file.

Through optimized data processing, CData ODBC Drivers offer unmatched performance for interacting with live Salesforce data in Microsoft Power Automate. When you issue complex SQL queries from Power Automate to Salesforce, the driver pushes supported SQL operations, like filters and aggregations, directly to Salesforce and utilizes the embedded SQL engine to process unsupported operations client-side (e.g. SQL functions and JOIN operations).

About Salesforce Data Integration

Accessing and integrating live data from Salesforce has never been easier with CData. Customers rely on CData connectivity to:

Access to custom entities and fields means Salesforce users get access to all of Salesforce.
Create atomic and batch update operations.
Read, write, update, and delete their Salesforce data.
Leverage the latest Salesforce features and functionalities with support for SOAP API versions 30.0.
See improved performance based on SOQL support to push complex queries down to Salesforce servers.
Use SQL stored procedures to perform actions like creating, retrieving, aborting, and deleting jobs, uploading and downloading attachments and documents, and more.

Users frequently integrate Salesforce data with:

other ERPs, marketing automation, HCMs, and more.
preferred data tools like Power BI, Tableau, Looker, and more.
databases and data warehouses.

For more information on how CData solutions work with Salesforce, check out our Salesforce integration page.

Getting Started

Connect to Salesforce as an ODBC Data Source

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

There are several authentication methods available for connecting to Salesforce: OAuth, Login (or basic), and SSO. The Login method requires you to have the username, password, and security token of the user.

OAuth Authentication (default)

The default authentication mechanism (and the one preferred by Salesforce) is OAuth. To use OAuth with CData's embedded OAuth application, leave the connection properties blank. If you have configured your own custom OAuth application with Salesforce (see the Help documentation for more information), set OAuthClientId, OAuthClientSecret, and CallbackURL to the properties for you application. Set InitiateOAuth to the desired OAuth flow ("GETANDREFRESH" will have the connector manage the entire OAuth flow).

Login (or Basic) Authentication

If you do not wish do not wish to use OAuth authentication, you can use Login (or basic) authentication. Set AuthScheme to Basic, and set the User, Password, and SecurityToken properties. You can configure your security token in Salesforce.

SSO (single sign-on) Authentication

SSO (single sign-on) can be used by setting the SSOProperties, SSOLoginUrl, and SSOExchangeURL connection properties, which allow you to authenticate to an identity provider. See the "Getting Started" chapter in the Help documentation for more information.

Multi-Factor Authentication (MFA)

If your Salesforce org has MFA enforcement enabled, set MFACode to the time-based one-time passcode (TOTP) generated by your authenticator app (such as Salesforce Authenticator or Google Authenticator). MFACode applies to both OAuth and Login authentication flows.

When you configure the DSN, you may also want to set the Max Rows connection property. This will limit the number of rows returned, which is especially helpful for improving performance when designing workflows.

Integrate Salesforce Data into Power Automate Workflows

After configuring the DSN for Salesforce, you are ready to integrate Salesforce data into your Power Automate workflows. Open Microsoft Power Automate, add a new flow, and name the flow.

👁 A new flow in Power Automate

In the flow editor, you can add the actions to connect to Salesforce, query Salesforce using SQL, and write the query results to a CSV document.

Add an Open SQL Connection Action

Add an "Open SQL connection" action (Actions -> Database) and configure the properties.

Connection string: DSN=CData Salesforce Source

After configuring the action, click Save.

👁 A configured 'Open SQL connection' action

Add an Execute SQL Statement Action

Add an "Execute SQL statement" action (Actions -> Database) and configure the properties.

Get connection by: SQL connection variable
SQL connection: %SQLConnection% (the variable from the "Open SQL connection" action above)
SQL statement: SELECT * FROM Account

After configuring the action, click Save.

👁 A configured 'Execute SQL statement' action

Add a Write to CSV File Action

Add a "Write to CSV file" action (Actions -> File) and configure the properties.

Variable to write to: %QueryResult% (the variable from the "Execute SQL statement" action above)
File path: set to a file on disk
Configure Advanced settings as needed.

After configuring the action, click Save.

👁 A configured 'Write to CSV file' action

Add a Close SQL Connection Action

Add a "Close SQL connection" action (Actions -> Database) and configure the properties.

SQL Connection: %SQLConnection% (the variable from the "Open SQL connection" action above)

After configuring the action, click Save.

👁 A configured 'Close SQL connection' action

Save & Run the Flow

Once you have configured all the actions for the flow, click the disk icon to save the flow. Click the play icon to run the flow.

👁 A fully configured workflow

Now you have a workflow to move Salesforce data into a CSV file.

👁 Application data in a CSV file (Salesforce is shown)

With the CData ODBC Driver for Salesforce, you get live connectivity to Salesforce data within your Microsoft Power Automate workflows.

URL: https://www.cdata.com/kb/tech/salesforce-odbc-power-automate.rst