Use Dataflow Gen2 for multiple tables ingest dinamically

Yes it is possible to ingest data from an on-prem SQL Server with only one DFg2 (but only if the firewalll is configured properly, otherwise you'll 2 DFg2 chained together), which is what we are doing too.  And unless this has changed, from the Microsoft doc, DFg2 is the only way currently to do so, until Microsoft will release the new OPGW (on-prem gateway). ETA is supposed to be sometime in Q1. Then it will be possible to do use a Copy activity inside a pipeline instead of a DFg2, if I remember correctly. In any case, we wait expectantly.

But first you have to figure out how to config your firewall.  Also, you need to know how the data actually moves between a DFg2 and the GW. Here is the excellent explanation from pqian_MSFT :  Re: Dataflow refresh started failing today MashupE... - Page 2 - Microsoft Fabric Community

And here is my post explaining about the firewall config: DATAFLOW and ON-PREM DB connectivity solution - Microsoft Fabric Community 

but in a nutshell, the firewall should allow you to specify that, for outgoing traffic for protocol TCP, you want to open port 1433, and only for the following destinations (the wildcard FQDNs below); but if that doesn't work, then destination can be set to: 0.0.0.0, which means the outgoing traffic is not restricted to any specific IP address: 

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

Oh! I see what you mean. I just asked Bing Copilot and it says it's possible from a PySpark notebook. But, as one commenter says on the LinkedIn page below, this would mean opening your DB port 1433 to incoming traffic, and if it's a production DB, it is doubtful your sysadmin will agree due to the security risk.  Here' what Copilot said:

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()
df = spark.read.format('jdbc').options(
    url='jdbc:your_database',
    dbtable='your_table',
).load()

EDIT_0:  actually, perhaps this might not work due to the huge amount of data that has to be passed around. I don't know the limits put on the HTTP Request action of Power Automate. 

------------------------------------------------------------------------------------------------

But I got another idea!  How about creating a Power Automate flow that would ingest the data from the on-prem DB, and then inside the pipeline, you would use a Webhook activity to connect to that Power Automate flow and pass the data to your Notebook and do everything in PySpark?  That way there is not need to change your firewall rules, as Power Automate uses the gateway.

Calling Microsoft Flow from your application | Power Automate Blog

Chris Webb's BI Blog: Calling Microsoft Flow From Power Query And Power BI (crossjoin.co.uk)

I asked Bing Copilot and got this:

Yes, but the table ingest is not dynamic. Every time I have new table, I have to add it manuallu to my DFg2 and publish it with the resto of the tables.

I've heard that in 1-2 weeks on-prem SQL Server data ingestion with gateway will be available for Copy activity. I guess this update will be so helpful for me, because source and destination can me parametrised in Copy activity. I think I'm going to wait until this feature comes up.

Sorry, maybe I didn't ask the question correctly. Yes, I know that with DFg2 I can do ingests via gateway, in fact, that's how I'm doing it now and I have everything configured to work.

My question was, since you tell me to do the ingest with notebooks because otherwise I can't parameterise this process, as the number of tables is variable, can I do the ingest using gateway by notebooks? I think the answer is no, and in that case I understand that the solution you gave me of doing the whole process from the ingestion with notebooks does not work for me.

Am I wrong?

So it looks like you'll have to do everything using the M language in a DFg2 after all.  

Actually, now that I think a little more on this... here is a potential solution.  You need to ingest everything into one staging table using M in a DFg2.  So the output of DFg2 is going to be one table containing the data of all the tables you need to ingest for this cycle. 

Prerequisites:  at the source, ie the on-prem DB, create a metadata table to keep track of all the tables that need processing. 

Here is the algo:

0__load the metadata table

1__iterate over each source table (using the each _ keyword) and extract the data needed as per some incremental timestamp

2__the ingested data from each table is a table and as such it is put into a Power Query table where each row consists of one column holding the source table name, and a second column (of type Table) holding the data just injested during the iterative cycle

3__the output of the M script should be a table of tables, like so:

Your DFg2 has one output as a table. Publish that to a staging table in your LH.  Use a PySpark notebook that now can get access to this data, and have a Python script extract each source table from that one staging table and write the rows out to their corresponding LH production table.

amaaiia I just stumbled upon this page:  Lakehouse management API - Microsoft Fabric | Microsoft Learn  and read it quickly, but if I am not mistaken, you can load data to a lakehouse table using the lakehouse REST API.  

So, couldn't you at the source DB export the data you want in the LH as multiple CSV files; put these files on a secure file server;  use either Python locally to upload the CSV files to the LH, or use Power Automate to do the same?

Or if T-SQL allows, do all this from a stored procedure connecting to the LH REST API.

Hi amaaiia 

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.

Look what I found!  

Use this capability to make your data flows general-purpose, flexible, and reusable. You can parameterize data flow settings and expressions with these parameters.

I am assuming the Fabric Data Factory is just a rebranding of Azure Data Factory, so it should work the same.

Parameterizing mapping data flows - Azure Data Factory & Azure Synapse | Microsoft Learn