Copy activity in ADF errors out while using dynamic range partitioning

Question

Hey,

I've been trying to copy some data using the Copy Activity from SQL Server 16 to Snowflake through a self hosted IR that has direct Line of Sight to the Server. I'm trying to enable dynamic range partitioning on one of the column so that the reads are in parallel, but every time I try to pass a partition column (even with/without the lower and upper bounds) the pipeline throws the following error -
ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'dictionary' is invalid: 'Value cannot be null. Parameter name: dictionary'.,Source=,''Type=System.ArgumentNullException,Message=Value cannot be null.

I checked the logs and the log file that is being created looks like - sql_some_table_name_crt_ts_NA_09242023062959_0.txt
which makes me feel that while the activity does figure out the upper bound and lower bound on its own while it lists the source - when it splits it into ranges, the lower bound of first range is being sent as NA. Which in turns gives the error message I'm seeing. This feels like an inescapable bug.

Could anyone please help me figure out why I could be getting this error or if anyone's faced this before - how did you solve it?

Thanks!

Answer 1

Hi Putatunda, Rishav,

Thanks for posting your question in the Microsoft Q&A forum.

Ensure that the partition column is of a suitable data type for range partitioning. Typically, numeric or date/time columns work best. If using a string column, make sure it can be properly ordered.

Check if the partition column contains any NULL values or unexpected data that might interfere with the partitioning logic.

While ADF can automatically determine partition bounds, you might try explicitly setting the lower and upper bounds to see if it resolves the issue.

If you're using a custom query in the source, ensure it doesn't interfere with the partitioning logic.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Answer 2

Hi @Putatunda, Rishav ,

Welcome to Microsoft Q&A platform and thanks for posting your query here .

It seems you are getting an error : "Value cannot be null" in your pipeline where you are trying to use dynamic range partition in copy activity of ADF.

From the error message it seems that there is no value passed for the parameter you have created called 'dictionary' . Kindly provide the same and try again.

For setting the lower and upper bound explicitly , kindly make use of lookup activity .The Lookup Activity would return the Upper Bound and Lower Bound over partition column/expression which can be further utilized in the copy activity as below : 

select Max(@{pipeline().parameters.dynamicpartitioncolumn}) as UpperBound, Min(@{pipeline().parameters.dynamicpartitioncolumn}) as LowerBound from @{pipeline().parameters.schemaname}.@{pipeline().parameters.tablename}

Reference: https://techcommunity.microsoft.com/blog/fasttrackforazureblog/leverage-copy-data-parallelism-with-dynamic-partitions-in-adfsynapse-metadata-dr/3692133

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou