Forum Discussion

amaaiia's avatar
amaaiia
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

Use Dataflow Gen2 for multiple tables ingest dinamically

I have a Data Pipeline to ingest in my Lakehouse several tables located in an on-premise SQL Server, and the tables are going to be daily reloaded incrementally. They have a date field, and every day...
data engineering
Data Integration
power bi
Element115's avatar
Element115
Icon for Memorable Member rankMemorable Member
2 years ago

amaaiia How about this approach:

 

CREATE OR ALTER VIEW vTABLE_NAMES 
AS 
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
;

 

 

If you run this SQL script from your SQL analytics endpoint, it will create a view in your LH. Then from you DF, all you have to do is create a query that uses this view as its source.  The view return a column of all the tables in the dbo schema of the LH as a table.  Now your M code can reference this table and Bob's your uncle.

v-cboorla-msft's avatar
v-cboorla-msft
Icon for Microsoft Employee rankMicrosoft Employee
2 years ago

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.
Otherwise, will respond back with the more details and we will try to help.


Thanks.