Hello, I'm running an Azure Data Factory that copies multiple tables from on prem SQL server to an Azure Data Lake. So, I set many Copy activities through Az Data Factory Designer to execute parallel copies (each activity is carrying on the extract of one table). For better resources optimization, I would like to know if there is a way to copy multiple tables with one Copy activity ? I heard of "degree of copy parallelism", but don't know how to use it ? Rgds,

For better resources optimization, I would like to know if there is a way to copy multiple tables with one Copy activity ? No, each copy activity will copy single table. To copy 10 tables data, you would need to run 10 copy activities I heard of "degree of copy parallelism", but don't know how to use it ? This is to increase maximum number of threads. for ex. if we copy a folder from one data lake to another, increasing this number will increase the copy throughput as it will copy more number of files at once. For reference - copy-activity-performance-features For better orchestration and to avoid creating pipeline, copy activity every time - you can have a flow based on control table as shown here - tutorial-bulk-copy-portal ---------- Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Azure Data Factory - Degree of copy parallelism

Accepted answer

Vaibhav Chaudhari 38,616 Reputation points

2020-08-19T14:32:16.93+00:00

For better resources optimization, I would like to know if there is a way to copy multiple tables with one Copy activity ?

No, each copy activity will copy single table. To copy 10 tables data, you would need to run 10 copy activities

I heard of "degree of copy parallelism", but don't know how to use it ?

This is to increase maximum number of threads. for ex. if we copy a folder from one data lake to another, increasing this number will increase the copy throughput as it will copy more number of files at once.

For reference - copy-activity-performance-features

For better orchestration and to avoid creating pipeline, copy activity every time - you can have a flow based on control table as shown here - tutorial-bulk-copy-portal

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
Please sign in to rate this answer.

2 people found this answer helpful.
CloudRock 366 Reputation points

2020-08-20T14:20:19.677+00:00

Hello @Vaibhav Chaudhari

Thanks for explanation.

I walked through the article and see how to make ForEach activity. For 10 tables copy (ex.), How many copy activities should be set under ForEach activity ?

In the article, I saw an initiation with first table from the source dataset and then input the following query in source section : SELECT * FROM [@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}] which should initiate the copy of the all present tables.

But the run failed.

Rgds

Vaibhav Chaudhari 38,616 Reputation points

2020-08-20T14:31:23.067+00:00

In foreach activity, you should have only one copy activity. The flow should be metadata driven (by control table)

This video is good for reference - Azure Data Factory | Copy multiple tables in Bulk with Lookup & ForEach

If you are comfortable with this metadata driven flow, go for it. Otherwise you can also go with individual pipelines, copy activities separately and run them in parallel.

HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-08-20T17:51:29.4+00:00

Hi @bassamballaji-4529 ,

Not sure if you have already looked into this . May be you are trying to do something similar .

https://social.msdn.microsoft.com/Forums/en-US/d3d414b2-79b0-4e91-9335-e4c93675443a/dynamic-loading-tables-while-ignoring-conflicing-columns-dynamic-schema-mapping?forum=AzureDataFactory

Let me know how it goes .

Thanks
Himanshu

CloudRock 366 Reputation points

2020-08-21T13:05:41.963+00:00

Hi

The video explains well what I'm trying to do.

Best Rgds,

Samy Abdul 3,366 Reputation points

2021-09-28T07:53:23.997+00:00

Hi @Vaibhav Chaudhari ,your comment "No, each copy activity will copy single table. To copy 10 tables data, you
would need to run 1 copy activities" got me really confused here. Where as below thread explicitly mentions that " To use one Copy activity for multiple tables, you'd need to wrap a single parameterized Copy activity in a ForEach
activity. The ForEach can scale to run multiple sources at one time by setting isSequential to false and setting the batchCount value to the number of threads you want. The default batch count is 20 and the max is 50. Copy Parallelism on a single Copy activity just uses more threads to concurrently copy partitions of data from the same data source. "

https://stackoverflow.com/questions/63488899/azure-data-factory-degree-of-copy-parallelism

Which I also believe should be the case. Could you please clarify. Thanks

Srikanth Thota 1 Reputation point

2022-03-31T05:43:50.257+00:00

Hello @Samy Abdul ,

"No, each copy activity will copy single table. To copy 10 tables data, you would need to run 1 copy activities"

The above two statements from @Vaibhav Chaudhari are correct, Inside For each you need to use one copy activity but that doesn't mean in single copy activity it will copy multiple tables. for ex:10 tables ,For each activity will iterate 10 times and it will run copy activity 10 times thus it will copy 10 tables.

Hope this clarified.

Regards
Srikanth

Please feel free to vote the answer if its helped you.
Sign in to comment

Azure Data Factory - Degree of copy parallelism

0 additional answers