transfer multiple copy activity to a Stored procedure and assigning tasks

Chandrasekaran Sneha 0 Reputation points

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,047 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Muhammad Umair 80 Reputation points

    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.

    0 comments No comments

  2. Samy Abdul 3,361 Reputation points

    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

    0 comments No comments