Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

[jlakshminaraya]

Hello - 

I noticed that, data read from On-prem SQL server to Fabric Datawarehouse is slower than SSIS approach. 

Here are more details for better understanding about the issue: 

With SSIS approach: 

Source : SQL server database  

Destination : SQL server 

Count of tables : 11 

Volume of all 11 tables is around 200,000

ETL : SSIS dataflows (1 dataflow for each table)

Total time taken to read and load data in on-premise for all 11 tables is 50 seconds

With Fabric DF GEN2 approach : 

Source : SQL server database  

Destination : Synapse Warehouse

Count of tables : 11 

Volume of all 11 tables is around 200,000

Pipeline : DF GEN2 (11 tables under single DF GEN2)

Total time taken to read and load data in Synapse Warehouse is 17 minutes.

Looking forward for a solution to improve performance of DF GEN2.

Thanks,

Lakshmi

[pqian_MSFT]

@jlakshminaraya - can you post the request ID for the 17mins/11 table DFG2 refresh. You can obtain it from the refresh history page.

[v-cboorla-msft]

Hi @jlakshminaraya 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[v-cboorla-msft]

Hi @jlakshminaraya 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.

[Element115]

@jlakshminaraya Also, I got a question.  Do you use one or two chained dataflows to do your ingestion from the on-prem DB to the synapse warehouse?

I ask because I am unable to ingest anything from an on-prem DB via the Power BI data gateway to a Fabric lakehouse or warehouse if I only use one dataflow.  I have to use the workaround Microsoft recommends.  This workaround consist of using 2 dataflows. The first one connects to the on-prem DB and ingest the data. And the second dataflow uses the first dataflow as a source and loads the data to a Fabric destination.

[jlakshminaraya]

I have one dataflow only. Which is working firn for me

[Element115]

@jlakshminaraya Could you please let me know what your endpoints are in your firewall rules?

This?

• Endpoints: *.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com

Or something else? like an additional endpoint perhaps that's not listed in the docs?

[Element115]

That's amazing! Why do I need to use 2 DFs???  I don't get it.  Did you also set your firewall rules to this or do you have different endpoints?

• Protocol: TCP
• Endpoints: *.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com
• Port: 1433

That's straight from the documentation... but if the docs are not in sync with some new changes, then that might explmain perhaps I am missing an endpoint?

source: On-premises data gateway considerations for data destinations in Dataflow Gen2 - Microsoft Fabric | ...

[Element115]

@jlakshminaraya  To get more traction for your idea, also post it here please:  Fabric suggestions and ideas