CODEWITHSUNDEEP
azureazure-sql-databaseazure-data-factoryazure-synapse
Linus Proxy
Linus Proxy

Reputation: 592

scheduled 1:1 copy of tables between Azure SQL databases using Data Factory without specifying table schema

Using Azure Data Factory, is it possible to perform a scheduled 1:1 copy/clone of all rows from multiple (not all) tables residing in an Azure SQL DB to another Azure SQL DB (in this case an Azure SQL Data Warehouse), without having to explicitly define all the table schema/structure in the Data Factory JSON datasets?

Reason I'm asking is that the Data Factory tutorials I've seen so far (such as this one) have explicitly defined table schemas for their copy activities. If we've got hundreds of tables that we want to copy, we would have to spend days writing table definitions in JSON (or script it, I guess, either case it seems like an unneccessary work effort for simply copying data).

(I should add that also the On Premise SQL -> Azure DB/DW case with ADF is of interest. Would really like to be able to periodically and affordably move data to the cloud without having to specify thousands of table schemas. ).

Upvotes: 3

Views: 1571

Answers (2)

lojkyelo
lojkyelo

Reputation: 121

Yes, using the Copy Data tool you can accomplish this - once you have configured your connection to the source - you can use the wizard to select individual tables and / or views to process:

copy-data-wizard-source

If you do not want to use the UI to select the desired entities, you can source the tables with a query of the sys objects

select schema_name(t.schema_id) as schema_name,
       t.name as table_name
from sys.tables t
where (schema_name(t.schema_id) not in ('schema-i-dont-want') 
       or t.name not in ('table-i-dont-want'))
order by table_name;

You will then configure your destination, the default target entity name will match the source name. You can skip column mapping at this step also - ADF will attempt to like for like columns - you can verify supported data types in the MS documentation (https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping).

copy-data-wizard-sink

From there the wizard is self explanatory.

Upvotes: 0

Matt Usher
Matt Usher

Reputation: 1325

Azure Data Factory does not require the schema or structure definition but the data set(s) need to be defined for all the tables that are to be copied. You'll have to define the data set (with or without schema) for all tables that require copy for any source-destination combination.

Upvotes: 4

Related Questions