transfer multiple copy activity to a Stored procedure and assigning tasks

Question

To Optimize the cost compared to copy activity in ADF by using stored procedure and tasks

Answer 1

The choice between using a stored procedure and copy activity in ADF to optimize costs depends on your specific use case, including data volume, complexity, frequency, and cost considerations. You may also choose to combine both methods if it makes sense for your data workflow, using stored procedures for data transformation and copy activity for efficient data movement.

Answer 2

Hi @Chandrasekaran Sneha , Please bear in mind, as per Microsoft Azure documentation, If you configure a stored procedure in the sink, the database will apply the data row by row instead of bulk load, so the performance will drop significantly. If the size of data is large, when applicable consider switch to using the “sqlWriterCleanupScript ” property (see below) instead.

I believe, your data source resides on the on-prem database, Hence, optimizing IR also comes into the picture, when using self-hosted IR, you can take either of the following approaches:
Manually scale up the machine.
Scale-out to multiple machines (up to 4 nodes), and a single copy activity will partition its file set across all nodes. Thanks