sql server - Copy Activity in ADF errors out when I use Dynamic Range Partitioning for concurrents reads and writes - Stack Over

admin管理员组

文章数量:1335887

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!

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!

• sql-server
• azure
• snowflake-cloud-data-platform
• azure-data-factory
• sql-server-2016

Share Improve this question Follow edited Nov 19, 2024 at 21:59 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Nov 19, 2024 at 21:56 Rishav PutatundaRishav Putatunda 3311 silver badge33 bronze badges 2

• check if you are passing null value to any parameter – Pratik Lad Commented Nov 20, 2024 at 3:18
• Hey @PratikLad, thanks for the reply - as you can see from the resolved input, none of the parameters are being passed as null - atleast until the input to the copy activity. – Rishav Putatunda Commented Nov 20, 2024 at 5:48

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

,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

本文标签：