CODEWITHSUNDEEP
sql-serverazuresnowflake-cloud-data-platformazure-data-factorysql-server-2016
Rishav Putatunda
Rishav Putatunda

Reputation: 33

Copy Activity in ADF errors out when I use Dynamic Range Partitioning for concurrents reads and writes

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

The resolved input to the copy activity is as follows -

{
    "source": {
        "type": "SqlServerSource",
        "additionalColumns": [
            {
                "name": "ADF_RUN_ID",
            }
        ],
        "sqlReaderQuery": "select somecolumns (that includes crt_ts)  from some_table where ?DfDynamicRangePartitionCondition",
        "queryTimeout": "02:00:00",
        "isolationLevel": "ReadCommitted",
        "partitionOption": "DynamicRange",
        "partitionSettings": {
            "partitionColumnName": "crt_ts",
            "partitionUpperBound": "2024-10-16T12:59:59Z",
            "partitionLowerBound": "2022-09-01T00:00:00Z"
        }
    },
    "sink": {
        "type": "SnowflakeV2Sink",
        "preCopyScript": "",
        "importSettings": {
            "type": "SnowflakeImportCopyCommand",
            "additionalCopyOptions": {
                "ON_ERROR": "ABORT_STATEMENT"
            },
            "additionalFormatOptions": {
                "TIME_FORMAT": "'0:HH:MI:SS.FF7'"
            }
        }
    },
    "enableStaging": true,
    "stagingSettings": {
        "linkedServiceName": {
            "referenceName": "",
            "type": "LinkedServiceReference"
        },
        "path": "staging"
    },
    "enableSkipIncompatibleRow": false,
    "logSettings": {
        "enableCopyActivityLog": true,
        "copyActivityLogSettings": {
            "logLevel": "Warning",
            "enableReliableLogging": false
        },
        "logLocationSettings": {
            "linkedServiceName": {
                "referenceName": "",
                "type": "LinkedServiceReference"
            },
            "path": "logging"
        }
    }

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 - but 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?

Many thanks!

Upvotes: 0

Views: 168

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8412

,Message=The value of the property 'dictionary' is invalid: 'Value cannot be null. Parameter name: dictionary'

I have tried the same in my environment with sample data it is working fine please check the data you are using is correct.

The above error can cause due to several reasons:

  • The parameter created in pipeline and the parameter used in source are case sensitive. To avoid this problem, keep same parameter name in both locations the same
  • Check the partition bounds (partitionLowerBound and partitionUpperBound) are correctly formatted and fall within the range of your data if it is not falling in range of your data, you can get such error.
  • Check with the static values (hardcoding it in the pipeline) if it works successfully then try with dynamic parameters.

If still issue persist, please raise support ticket here

Upvotes: 0

Related Questions