topic Re: Data Factory - SQL Server Source (Gateway) in Pipelines

Data Factory - SQL Server Source (Gateway)

alfBI — Fri, 09 Jun 2023 03:13:20 GMT

Hi,

I am trying to develop a Fabric Data Factory Pipeline getting data from a source placed on a on-premise SQL Server.

But when I establish the connection I don't see the way to define the gateway (as it should, otherwise I have no idea how it is expected he found it 🙂 )

Any idea about what am I missing here to use a on-premise database source on a Fabric Data Factory Pipeline?

Regards

Re: Data Factory - SQL Server Source (Gateway)

vgvozdev — Mon, 29 May 2023 01:26:25 GMT

Hi,

I have the same issue. What is the proper way to connect to the On-Prem SQL Server?

Re: Data Factory - SQL Server Source (Gateway)

suidino2 — Mon, 29 May 2023 02:41:13 GMT

I think they either making a mistake about the SQL SERVER connector (it seems the connector setting is to azure instead of on-premise) or they assume us to use Dataflow? to pulling data from on-premise server.

Btw, dataflow gen 2 does work due to gateway problem.

Re: Problem running a gen2 dataflow from a non azu... - Microsoft Fabric Community

Re: Data Factory - SQL Server Source (Gateway)

GraceGu — Mon, 29 May 2023 05:25:21 GMT

Pipeline copy on-prem access (by SHIR in ADF) is not available yet. You can refer to Differences between Data Factory in Fabric and Azure - Microsoft Fabric | Microsoft Learn to get an idea on what are there and what are not. Right now, you can use dataflow to access on-prem resources.

Re: Data Factory - SQL Server Source (Gateway)

alfBI — Mon, 29 May 2023 06:17:07 GMT

Hi,

Thx for the link. I missed it. Effectively there is some significant difference with ADF with regard integrated runtimes

good news are that on-premise data gateway support feature seems to be on the roadmap and currently in progress. So we will have to wait a bit or use Dataflows as workaround.....

Thx

Re: Data Factory - SQL Server Source (Gateway)

ScottPowellECU — Thu, 01 Jun 2023 21:37:26 GMT

I have no experience with ADF as we don't really use Azure. I'm super curious - for folks that have used both ADF and Dataflows via an on-prem gateway, is ADF significantly faster?

It takes a pretty long time to copy data across via our gateway servers. I'm hoping ADF is at least an order of magnitude faster.

Thanks,

Scott

Re: Data Factory - SQL Server Source (Gateway)

germo — Fri, 09 Jun 2023 16:22:55 GMT

is there a roadmap when the "On-premises Data Gateway(in design)" could be available?
Are there any workarounds to copy data from on premise to fabric?
Power BI has a gateway, it works fine. But it can't be used with fabric.

Or maybe there is am easy way to first export from on premise SQL Server into Parquet file and then to upload it?

Re: Data Factory - SQL Server Source (Gateway)

germo — Sun, 11 Jun 2023 12:03:53 GMT

I created a "Dataflow Gen2", which works like Power Query. I used the "On-premises data gateway" as source, Lakehouse as destination, and this works fine.