Copy multiple SQL tables with same structure from different Azure SQL DBs, Combine and insert into Target Azure SQL DB

Question

I am pretty new to ADF and need help with requirement where I need to combine data from different SQL DBs and insert into target DB so I want to use ADF to ingest and transform the data.

Source Data

SQL DB1 (Country A) : Table name : Test
SQL DB2 (Country B) : Table name : Test
SQL DB3 (Country B) : Table name : Test
SQL DB4 (Country B) : Table name : Test

Target Data

SQL DB : Table name : Test (all data from listed DBs with one extra column which identify the Country Name)

Also, I would like to know how we can scale up this in future when we add some more countries with minimum manual efforts and how to do incremental copy

Could you please suggest or share reference link to achieve this use case using ADF ? Thanks

I have tried using copy activity but I am missing something

Answer 1

You can use DataFlow activity. In Source select Set partitioning.

Select Key as Partition type and Country column as Unique value per partition.