getting error in copy data activity while using upsert

Question

the pipeline is

{

"source": {

    "type": "DelimitedTextSource",

    "storeSettings": {

        "type": "AzureBlobStorageReadSettings",

        "recursive": true,

        "enablePartitionDiscovery": false

    },

    "formatSettings": {

        "type": "DelimitedTextReadSettings"

    }

},

"sink": {

    "type": "AzureSqlSink",

    "writeBehavior": "upsert",

    "upsertSettings": {

        "useTempDB": true,

        "keys": [

            "order_id"

        ]

    },

    "sqlWriterUseTableLock": false,

    "disableMetricsCollection": false

},

"enableStaging": false,

"translator": {

    "type": "TabularTranslator",

    "mappings": [

        {

            "source": {

                "type": "String",

                "ordinal": 1

            },

            "sink": {

                "name": "order_id",

                "type": "Int32",

                "physicalType": "int"

            }

        },

        {

            "source": {

                "type": "String",

                "ordinal": 2

            },

            "sink": {

                "name": "order_date",

                "type": "String",

                "physicalType": "varchar"

            }

        },

        {

            "source": {

                "type": "String",

                "ordinal": 3

            },

            "sink": {

                "name": "order_customer_id",

                "type": "Int32",

                "physicalType": "int"

            }

        },

        {

            "source": {

                "type": "String",

                "ordinal": 4

            },

            "sink": {

                "name": "order_status",

                "type": "String",

                "physicalType": "varchar"

            }

        }

    ],

    "typeConversion": true,

    "typeConversionSettings": {

        "allowDataTruncation": true,

        "treatBooleanAsNumber": false

    }

}

}

its working if i choose insert and getting below error while using upsert

Code": 11000,

		"Message": "Failure happened on 'Sink' side. 'Type=System.NullReferenceException,Message=Object reference not set to an instance of an object.,Source=Microsoft.DataTransfer.Connectors.MSSQL,'",

Answer 1

For some reason, I have the same problem.

Note that this pattern is something that I have used in more than 50 other pipeline. I have used it across import xml files and json. There is nothing different at all except for field names.

The process is as follows

1. Create a table in sql server
2. In the table say "timesheets", have a unique index eg timesheetId.
3. In the pipeline, import schemas, and perform all the required mappings
4. In the sink specify the timesheetId for the key, for upsert.
5. Map the Id column from the source to the timesheetID.

This seems to be a new bug. I dread to think what will happen if the other pipeline which use the same pattern start to fail.

Any help will be greatly appreciated.