Unanswered

Custom Data Provider

(0) Share

Report

Posted on by VitalyPopov

I need to develop Custom Data Provider that use On-Premises SQL Server Tables as Virtual Tables in Dataverse. Virtual Table Provider does not work. I know, it is possible, when use Azure SQL Database, but if i wont use local SQL instance? Can I use SQL Connection?

Categories:

Microsoft Dataverse

All responses (8)

Answers (0)

EricRegnier 8,714 Most Valuable Professional on at

Like (1)

Report

Re: Custom Data Provider

I'm not sure if it's possible... Your Sql connection string for the SqlConnection object would need to point to your gateway somehow but can't find any docs that says it's supported... From what I know implementing a custom data provider DLL and registering it to Dataverse is different form allowing custom connectors to the data gateway as explained here: https://learn.microsoft.com/power-bi/connect-data/service-gateway-custom-connectors. But I may be missing something....
TaraAssadipour 79 on at

Like (0)

Report

Re: Custom Data Provider

Thanks for your prompt answer @EricRegnier . The problem with the newish virtual data provider is it does not let me run a join query on the sql side to bring a lookup column into my model driven app. That's why I had to develop a custom virtual table provider (I don't want to use canvas app). I would appreciate it if you can tell me how I can associate my custom data provider with the on-prem data gateway.
EricRegnier 8,714 Most Valuable Professional on at

Like (0)

Report

Re: Custom Data Provider

The association with the data provider is a separate step/process. Once your data gateway is setup successfully and you can connect/use it (test with a simple flow using the Sql Server connector) you can configure your data provider. I recommend using the newish Virtual Data Provider instead of a custom one. More details on Virtual Data Provider: https://learn.microsoft.com/power-apps/maker/data-platform/create-virtual-tables-using-connectors
TaraAssadipour 79 on at

Like (0)

Report

Re: Custom Data Provider

Hi @EricRegnier ,

I have an on-pre data gateway already installed. My question is when/how/at what step I should associate it with the custom data provider? is it during the registration?
EricRegnier 8,714 Most Valuable Professional on at

Like (0)

Report

Re: Custom Data Provider

HI @VitalyPopov, the only way besides exposing your server to the public is with the Power Platform On-Premise Data Gateway. Here's the info and installation: https://learn.microsoft.com/data-integration/gateway/service-gateway-onprem?WT.mc_id=DX-MVP-5004271

Hope this helps!
TaraAssadipour 79 on at

Like (0)

Report

Re: Custom Data Provider

I have similar problem, what did you eventually do @VitalyPopov ? I wonder if there's a way we can associate a custom virtual table provider with a specific gateway during the registration process in the Power Platform environment.
VitalyPopov 36 on at

Like (1)

Report

Re: Custom Data Provider

Hello, @a33ik
Thank you.
I understand it. But my question was how to use the local server without publishing it to the outside. I would like to use the approach as a Virtual Provider. Create a connection to a local server using a gateway. It is possible?
a33ik 3,304 Most Valuable Professional on at

Like (0)

Report

Re: Custom Data Provider

Hello @VitalyPopov,
Technically there should be no difference between Azure SQL and regular SQL one thing you will have to do is to expose your SQL to the external world, i.e. make it available for the external calls. You can start here as a starting point - https://mikefactorial.com/2020/08/15/direct-query-sql-virtual-entities/