'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Connect Master Data Services to Power Platform Dataflows
Question details
The user wants to connect on-premises SQL Server Master Data Services (MDS) to a Power Platform dataflow to load data into Dataverse, or find an appropriate premium connector.
- Product
- Power Platform
- Device & OS
- not provided
- Scenario
- Connecting on-premises MDS data to Power Platform Dataverse via dataflows for data integration.
- Observed behavior
- The user needs a reliable method or a specific premium connector to route on-premises Master Data Services data into Dataverse.
Ensure you have administrative access to your on-premises SQL Server and the necessary permissions to configure an On-premises Data Gateway in your Power Platform environment.
Use SQL Server Connector and On-Premises Data Gateway
Since Master Data Services relies on an underlying SQL Server database, you can connect directly to the SQL Server views using an On-premises Data Gateway.
There is currently no dedicated Master Data Services connector in the Power Platform catalog. The most robust architectural solution is to query the underlying SQL Server database where your MDS data resides, leveraging the standard SQL Server connector.
Download and install the On-premises Data Gateway on a machine within the same local network as your SQL Server.
Sign in to the gateway installer with your Power Platform administrator credentials and register the gateway to your environment.
Log into Power Apps, navigate to Dataflows in the left pane, and select 'New dataflow'.
Choose the 'SQL Server database' connector from the data sources list, enter your server credentials, and select your newly configured on-premises data gateway.
Navigate through your MDS database and select the appropriate subscription views to map and load into Dataverse.
Consult Power Apps Community or Build a Custom Connector
If direct SQL access is restricted by your organization, you may need to rely on custom APIs and custom connectors.
Switch to WPS Office for a Seamless and Free Productivity Experience
While connecting enterprise databases like MDS to Power Platform requires specific Microsoft integration tools, your daily document, spreadsheet, and presentation tasks don't need to be tied to expensive subscriptions. WPS Office provides a lightweight, highly compatible alternative for all your core office needs.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the Suite: Run the downloaded executable file and follow the standard installation prompts to set up the software.
- 3. Open Existing Data: Launch WPS Spreadsheets to seamlessly open, edit, and analyze your existing Excel workbooks and exported CSV data sets.
Frequently Asked Questions
Is there a native Master Data Services connector in Power Platform?
Currently, there is no dedicated premium connector specifically named 'Master Data Services' in the standard Power Platform catalog. Integrations primarily rely on using the SQL Server connector pointing to the underlying MDS database.
Can I connect Dataverse directly to an on-premises database without a gateway?
No. To connect cloud-based Power Platform services like Dataflows to an on-premises SQL Server, you must install and configure an On-premises Data Gateway to securely bridge the networks.
How should I expose MDS data for extraction to Dataverse?
The best practice is to create Subscription Views within Master Data Services. These views provide a flattened, read-only layer that is much easier to query and map from external tools like Power Platform Dataflows.
