How to avoid transient issue: Hit unexpected exception, please retry.

This error in Azure Data Factory typically indicates a connectivity or resource bottleneck occurring during the pre-processing phase of a pipeline execution. The error source PrepareV5 refers to the internal engine component responsible for initializing the movement of data and establishing connections to the source or sink. Because you noted the issue is transient, it is most likely caused by a momentary timeout or a "throttling" event on the SQL database side rather than a permanent misconfiguration in your linked service.

When the ADF engine attempts to connect to a SQL source, it executes an initial schema discovery or metadata look-up. If the SQL instance is under heavy load, experiencing a brief failover, or hitting its concurrent connection limit, the database may terminate the request abruptly. This causes the pipeline to fail with a user configuration error because the engine assumes the provided credentials or connection string failed to establish a stable session.

To mitigate this in the future, consider implementing a retry policy within the activity settings of your pipeline. Setting the retry count to a value between one and three with an interval of thirty seconds often allows these transient "SQL Hit unexpected exception" events to resolve themselves without manual intervention. This should mitigate the risk that a temporary network flicker or a brief database locking period results in a total pipeline failure.

If the frequency of this error increases, you should monitor the DTU or vCore utilization on your Azure SQL Database during the time of the failure. You may find that your database is reaching its resource limits. In such cases, you might need to scale up your database tier or adjust the "parallel copies" setting in your ADF Copy Activity to reduce the number of simultaneous connections being opened against the SQL server.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin