Programmatic Data Sources?

[wcarter]

Right now I have a pipeline in data factory that imports from on-prem sql twice per day to a "warehouse", that appends all new and changed records from multiple databases (one for each of the last 10 or so years and increasing). Each year I need to add a new database to the list of sources and have all the same tables import from the new database as are imported from the current databases. This is done with a sql table that simply lists each database to be imported, which is used for a loop to feed the copy data step. Is there any similiar way to handle this with data flows / power query? I don't want to have to manually add the new year's database to every flow every year, this would turn a few seconds of work into a several hours and vastly increase the chances for a mistake to be made. I'm starting to look at Fabric, Data Flow Gen 2, Direct Lake PBI, etc, and trying to figure out how to get the same data available through all this interesting new framework.

Thank you

[wcarter]

So with Azure Data Factory I can do something like this, where I get the list of databases that need to have data pulled from (they are all identical, except iterated for each fiscal year), :

and then for each one run a loop that copies in data from specified tables to the sink database:

This is all accomplished using the Expression Builder to input dynamic values where needed.

With Dataflow Gen 2 I need to be able to populate the queries dynamically the same way. As you can see below in my first test of creating a flow, for a single table I had to add the last 10 years of a single table manually:

These all get combined into a single destination table which is then published to the lakehouse. I have ~60 or so such tables, each of which has 10 years of iterations that need to be brought in so that we can access the data with Direct Lake mode in Power BI. That's my current goal for utilizing Fabric, so if I can avoid having to update 60 different flows every fiscal year to add a new year's table to the list that would be great. There are also various csv and excel files that are used in some cases, but they are incidental to the core data.

[miguel · Accepted solution]

Assuming a few things like:

• All databases come from the same server
• The same authentication / credentials used has access to all databases 
• You have a logic in place to determine what tables to pick from each database correctly

You can use the SQL Database connector, enter just the server name and that will yield a table with all the available databases inside of the server. From there, you can create your own query with the custom logic of your own or even package your logic as a function that could run against every single database in that server and yield a single query that you could later sink / output / load to a destination of your choice.

The article below might be able to help you with how to create functions:

Using custom functions in Power Query - Power Query | Microsoft Learn

For the SQL Database connector, you can also simply use the function Sql.Databases:

Sql.Databases - PowerQuery M | Microsoft Learn

[wcarter]

Ahh! That's really good, I totally missed that I could just grab everything into one super query and then split off from it as needed. That sounds very doable and I'll give it a try asap. Thank you!

[Anonymous]

Hi @wcarter ,

Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

[Anonymous]

Hi @wcarter  - Thanks for using Fabric Community,

As I understand you would like to check for possibility whether we can create dynamic database in warehouse (i.e. sink) using Data Flow Gen 2 in Fabric.

Inorder to understand clearly can you please share few more details of your ask?

I will be able to guide you better if you can share few more details.