Forum Discussion
Use Dataflow Gen2 for multiple tables ingest dinamically
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.
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.
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:
TABLE_NAME DATA_FROM_SOURCE_TABLE_AS_TABLE TABLE_0 {[c0, c1, ..., cn], [d0, d1, ..., dn]} TABLE_1 {[c0, c1, ..., cn], [d0, d1, ..., dn]} ... ... TABLE_n {[c0, c1, ..., cn], [d0, d1, ..., dn]} 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.
