Create virtual tables using the virtual connector provider

Virtual tables enable integrating data from external data sources by seamlessly representing that data as tables in Microsoft Dataverse, without data replication. Solutions, apps, flows, and more can use virtual tables as if they were native Dataverse tables. Virtual tables allow for full create, read, update, and delete privileges unless the data source they're connecting to specifically forbids it. More information about virtual tables: Create and edit virtual tables that contain data from an external data source.

This document covers the new experience using Power Apps (make.powerapps.com) to create virtual tables using the following virtual connector providers:

• SQL Server
• Microsoft SharePoint
• Microsoft Fabric. More information: Build apps and automations, drive action with insights from Microsoft Fabric
• Salesforce (preview)
• Oracle (preview)

You can create a virtual table for Excel using the virtual connector provider by following the legacy process. More information: Create the virtual table for Microsoft Excel

To learn more about supported actions and limitations with the connector, go to:

• Connector reference for the SQL Server connector
• Connector reference for the Microsoft Excel Online Business connector
• Connector reference for the SharePoint Online connector
• Connector reference for the Salesforce connector
• Connector reference for the Oracle connector

Overview

Virtual tables include the following components:

• Data Source – the location where the external data is stored.
• Data Provider – defines the behavior of the virtual table.
• Connection – this sets up the ability to connect to the data source and authentication.
• Connection reference – this provides a way for Dataverse to use the connection to the data source.

If you were to create a virtual table using a custom data provider, you'll need to write plugins that define how every Dataverse API would interact with the API on the system where the data is stored. This is a long process that requires knowledge of coding. Virtual connector providers streamline the creation experience by automating some of the creation for you and removing the need to use code to create the virtual tables.

When you establish a remote connection to an external source using a connector data source, the virtual connector provider automatically retrieves a list of all the available tables and lists by retrieving table definitions (metadata) from the external data source. You then select these tables and lists to generate the virtual table.

The underlying data source is key for allowing the provider to establish an authenticated remote connection to the external data. It uses a connection reference that stores pertinent details regarding the external source. The information stored in the connection reference is specific to the connector type and the connection it refers to.

When setting up the connection and connection reference for your data sources, specific information is needed. For example, the SQL Server connector needs server name, database name, the authentication method, username, password, and (optionally) gateway connection details. Each external data source will need a connection reference defined to create the virtual table. When using the Power Apps (make.powerapps.com) experience, the connection reference can be generated automatically for you unless you wish to provide custom naming.

Note

• When you use the Excel virtual connector provider, an Entity Catalog table is generated by the system which provides a list of all the available tables using the table definitions (metadata) from the external data source. The entity catalog doesn't persist any information and always represents the external data source's current state. You can select tables from the entity catalog to create virtual tables. If you're working with multiple external data sources, an entity catalog is generated for each external source.
• Currently, you must create your own connection reference when you use the Excel virtual connector provider. More information: Create the virtual table for Microsoft Excel

The connector permissions enforce the ability for organizational users to access and operate on the virtual table. The connection can be shared with one user or can be shared with the entire organization. This allows users to access and operate virtual tables using a shared connection. By using security roles, virtual table access can be restricted to a specific set of users within your organization. You can even specify which roles have create, read, update, or delete privileges in this way.

Application lifecycle management (ALM) is supported for virtual tables created using the virtual connector provider. You can even create the virtual tables from directly within a solution when using Power Apps (make.powerapps.com). Virtual tables should be part of a managed solution along with the connection reference to distribute the solution. The solution can have other components, such as a model-driven app that uses virtual tables.

More information about application lifecycle management (ALM) and solutions:

• Application lifecycle management (ALM) in Microsoft Power Platform
• Solutions overview

Prerequisites

To create a virtual table, you must have a Microsoft Dataverse license through Power Apps or Microsoft Dynamics 365. Microsoft 365 or Teams licenses can't be used to create virtual tables.

Create a virtual table in Power Apps for SQL, SharePoint, Fabric or Salesforce

Creating a virtual table in Power Apps (make.powerapps.com) using the virtual connector provider includes the following steps:

1. Choose to create a table using an external data source
2. Create the virtual table
3. Create and select the connection reference (optional)
4. Choose your connection details and select your data SQL or SharePoint.
5. Configure your data
6. Configure column and table names (optional)
7. Complete the setup

Choose to create a table using an external data source

These steps describe how to create a virtual table from a solution. Use similar steps to create a virtual table by going to Data > Tables.

1. Sign into Power Apps, and then select Solutions in the left navigation pane. If the item isn’t in the side panel pane, select …More and then select the item you want.
2. Create a new solution or open an existing unmanaged solution.
3. On the command bar, select New > Table > Table from external data.

Create the virtual table

Watch a short video showing how to create a virtual table with the virtual connector provider.

1. In the New table from external data wizard you can either select an existing connection if you've one or choose to create a new connection.

   • If you want to use an existing connection, select the connection you want, and then select Next.
   • If you have an existing connection but wish to create a new one, select New connection on the command bar.
   • If you have no connections and wish to create a new connection, select +Add Connection.

   Important

   Connections that are shared with you aren't available for use with this feature. Only connections created by the current user appear in the virtual table wizard.

2. You're directed to a new tab in your browser. Select your authentication method. Depending on the authentication method selected, you'll be asked to provide credential information required to create the connection.

SQL Server

Important

These will be the credentials used for all authentication for the virtual table so use credentials with the correct level of permissions with SQL Server.

• Microsoft Entra: Select and sign in with your credentials.
• SQL Server: Server name, database name, user name, password, gateway (on-premises deployments only).

Microsoft SharePoint

Important

These will be the credentials used for all authentication for the virtual table so use credentials with the correct level of permissions in SharePoint.

Note

At this time virtual tables using the virtual connector provider wizard only support online versions of SharePoint.

• Select to Connect directly (cloud-services)

  

Microsoft Fabric (preview)

[This topic is pre-release documentation and is subject to change.]

Important

• This is a preview feature.
• Preview features aren’t meant for production use and may have restricted functionality. These features are available before an official release so that customers can get early access and provide feedback.

• Select a Microsoft Fabric workspace from the available list of workspaces. All workspace where you have access are available in the list.
• Choose a Microsoft Fabric Lakehouse from the drop-down list. All lakehouses and data warehouses within the workspace selected previously are available to choose.
• Follow the instructions on your screen. More information: Build apps and automations, drive action with insights from Microsoft Fabric (preview)

Salesforce (preview)

[This topic is pre-release documentation and is subject to change.]

Important

• This is a preview feature.
• Preview features aren’t meant for production use and may have restricted functionality. These features are available before an official release so that customers can get early access and provide feedback.

Select Add connection:

• Login URI: Select either Production (default) or Sandbox.
• Salesforce API version: Select v41.0 (default) or a later version.

Oracle (preview)

[This topic is pre-release documentation and is subject to change.]

Important

• This is a preview feature.
• Preview features aren’t meant for production use and may have restricted functionality. These features are available before an official release so that customers can get early access and provide feedback.

For Oracle connections, you must provide a server, authentication type, username, password, and a gateway.

Server should be provided in Server:Port/SID-format. Notice that the server name or IP-address needs to be accessible from the on-premises data gateway.

3. Select Create.
4. After the connection is created, go back to your browser tab with the wizard and select Refresh.
5. Select your connection.

Create and select a connection reference (optional)

When you create a virtual table, a connection reference is automatically created for you with the virtual table. A connection reference is a solution component that contains information about the connector.

However, you might want to create your own connection reference for the virtual table.

Note

• The benefit of optionally naming your connection reference is because it can be easier to find later if you need details about it.
• If you are using an existing connection you can select an existing connection reference or create a new one. Creating a new connection reference is only used if you want to segment your virtual tables into completely unrelated solutions for use later.

To create a connection reference, when you're creating the connection for the virtual table, follow these steps:

1. Expand Advanced Options and then select the Manually configure connection reference to create a connection reference for the virtual table.

   

2. On the Connection Reference page, select or name your connection reference, and then select Next.

   • If you chose SQL and Microsoft Entra ID as your authentication method, you'll be asked for your SQL server name and database name. Provide these and select Next.

Configure your Data

• If you're creating a SharePoint virtual table, you're asked to enter the URL of your SharePoint site or select from your most recently used SharePoint sites. The most recently used list is populated by gathering information about your recently used sites using Microsoft Graph and your Microsoft Entra credentials. If you're pasting the SharePoint URL, only include the information up to the site name, such as https://microsoft.sharepoint.com/teams/Contoso.

1. A page is displayed where you can either search your data source for a specific table or list, or select a table or list from the provided list.

2. Select the check box if you want to configure the table name, column names, and primary field.

3. Select Next.

Configure table and column names (optional)

When you create a virtual table, by default you can choose to change the suggested table and column names. To do this, follow these steps:

1. Select Configure table and column names that will be used in Dataverse, accept or change the following Dataverse table properties:

   • Display name: The name that will be used to identify your virtual table.
   • Plural name: The plural of the virtual table name, used in appropriate situations where you refer to one or more record from the table, such as Customer is the table for multiple records refereed to as Customers.
   • Schema name: The logical name Dataverse uses for the virtual table, which includes the solution publisher prefix.
   • Primary field: This is the text value to be used when looking up records on your virtual table. Only string fields can be selected. A primary key is a required field but will be chosen by Dataverse.

2. In the External column area, choose if you would like to rename any of your external columns from the data source. The following fields are provided:

   • Schema name (read-only). This is the schema name of the column in the data source. This property is read only.
   • Display name. The name that's used to identify your column.
   • Schema name. The logical name Dataverse will use for the column that will include your solution publisher prefix. There's a Quick Format Names option on the page, this will provide suggested name changes and can be useful if you have a large number of fields that include prefixed values from your SQL server such as <tablename>.<column name>. For example, Database12.Products would change to Products.

   Tip

   Instead of entering the information, the Quick format names command provides suggested name changes and can be useful if you have a large number of fields that include prefixed values from your SQL server, such as tablename.column name. For example, Database12.Products would change to Products.

   

3. Select Next

Complete the setup

1. The Review and Finish page shows you the table you're connecting to in your data source, and the table that will be created in Dataverse.

   Note

   Select Choose a different table takes you back to the table selection screen. selecting Edit Configuration of Table takes you to the Configuration screen.

2. If everything is correct, select Next.

Once the table is created, you're taken directly to your new virtual table where you can view your data and begin working with it.

Note

When you attempt to create a virtual table that already exists, you receive a message that the table already exists and that you will be re-creating it. You will not be able to change the primary field or schema name if this is the case. Re-creating the table will update any column changes that were made in the data source on the table.

Create the virtual table for Microsoft Excel

1. Download and install the virtual connector. Go to the Microsoft commercial marketplace and search for Virtual Connector or select the link to download the provider: Virtual connectors in Dataverse

   

2. Select Get it now. In the sign-in dialog, enter work or school account email. If you agree to the terms and conditions, select Continue. The Power Platform admin center will open automatically.

3. Select the environment where you want to install the solution. If you agree to the terms and conditions, select Install. Once the installation is complete, you'll see the Virtual connectors in Dataverse app installed under Environments -> [your environment name] -> Dynamics 365 apps.

   

4. You should also see the Virtual Connector Provider solution and other solutions enabled in the Power Platform environment.

   

Create the connection for the Excel virtual connector provider

Watch a short video showing how to create a virtual table with the Excel virtual connector provider.

1. Go to Power Apps (make.powerapps.com), and select the environment in which you would like to set up the virtual table.
2. In the left navigation pane, select Connections, and then select New connection. If the item isn’t in the side panel pane, select …More and then select the item you want.
3. Select the Microsoft Excel Online (Business) Virtual Connector from the list of connections.
4. You'll be asked to provide additional details to connect to the data source.
5. Select Create, your current signed-in credentials will be used.

Create the connection reference

1. Go to Solutions.

2. Select the Default Solution or any other existing solution you have in your environment to create the virtual table.

3. Select New and then select Connection Reference.

4. Enter Display name, select the connection you created for the Connectors option and then select the data connection that you've created.

   

Create the data source

Now create the virtual table data source in Dataverse.

1. Select the Gear icon -> Advanced Settings.

   

2. In the top navigation bar, select Settings and then Administration.

   

3. Select Virtual Entity Data Sources.

   

4. Select New. In the pop-up dialog, select the Virtual Connector Data Provider.

   

   

5. Name your Data Source and select the Connection Reference you created in the drop-down list.

   

   Add your Dataset Value

6. Paste in the file name including extension into the Dataset Value. Remember the file must be in the OneDrive that was used for the Connection setup. (for example: SampleData.xlsx)

7. Select Save.

Entity catalog

With the connection reference and the virtual table data source setup, an Entity Catalog is automatically generated. The Entity Catalog is specific to the data source and will list all the tables that are in the respective data source.

Note

• The creation of the entity catalog is an asynchronous process. Depending on your environment, this may take a few minutes.
• The tables displayed in the entity catalog are not virtual tables in themselves. You need to select from this list of tables representing the external data to create virtual table in Dataverse.

Tip

• If the entity catalog creation takes a long time, you can check the job completion status by navigating to Settings -> System Jobs view.

View the entity catalog

• Select Data > Tables, and then select the entity catalog that was created.

• Select Advanced Find and use the Look for: column. The catalog will include a prefix Entity Catalog for followed by the connection reference (example: Entity Catalog for Adventure Works DB). Find the entity catalog for your respective data connection and select Results to display all the external data source tables.

  

  Note

  Bulk creation of virtual tables is not supported currently. Even though the entity catalog allows you to select multiple tables, you will have to select one table at a time to create virtual tables.

1. To create a virtual table, a model driven app must be built for the entity catalog. Select the entity catalog table.

2. Select Create an app in the top navigation.

   

3. Name the app, and then select Create.

   

   The app is automatically generated using the entity catalog table.

4. Once the app is completed, you can select Publish to complete the app and use it later, or you can select Play to create your virtual table now without publishing the app.

   

   All tables from your Excel file will be provided in the app view.

5. Select the data set you wish to use from the entity catalog, and then select Edit in the navigation bar.

   

   Wait for the form to fully load before editing. When loaded the form will appear like this:

   

6. In the provided form set the Create or Refresh Entity column to Yes.

7. Select the Primary Key and Primary Field of the virtual entity by using the dropdown lists to find the columns you want to use.

   

8. Save the record to create the virtual table.

   Note

   After the save completes, the form will "reset" with all fields shown as blank, this is normal.

9. Return to the Power Apps home page and select Data. Your virtual table is now created with a "Custom Entity" prefix. It might take a few moments for the creation to complete.

   

Important

• The provider automatically maps the primary key associated with the external data source when creating the virtual table. All CRUD operations can be performed on the generated virtual table.
• All columns in the external data are automatically mapped to Dataverse types that are supported by the connector. You can review the virtual table details and make changes by navigating to Settings -> Customization – Entities view.
• Virtual tables require there to be at least one string field to use as the Primary Name column.

Once you've created a virtual table, you can work with it much the same way as any other table. You can start defining the relationships with other tables, in the environment and use them in your Power Apps and Power Automate flows.

See also

Setting up a virtual table relationship

Known limitations and troubleshooting with virtual tables

Developers Guide: Get started with virtual tables (entities)