getting error in copy data activity while using upsert

Question

getting error in copy data activity while using upsert

Rajesh 45

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,'",

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-05-22T22:12:41.19+00:00

Hello Rajesh Gangam,

Welcome to the Microsoft Q&A forum

This seems like a data issue.

Can you please check the upsert key column and make sure the key column doesnot contain the nulls or duplicate values?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-05-23T21:09:11.9666667+00:00

Hello Rajesh Gangam,

I am checking to see if you had a chance to look into my above response.
Rome Lin 10 Reputation points

2024-05-23T22:16:51.7166667+00:00

Hi BhargavaGunnam-MSFT,

I also ran into a similar issue with Azure Data Factory Upsert with the error message as follows:

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

I created a table first in Azure SQL database with a primary key and used this primary key for my upsert key column in Azure Data Factory. Somehow, it just will not work. Please take a look, I appreciate your feedback. Thanks
Rajesh 45 Reputation points

2024-05-23T23:28:09.6266667+00:00

Hi BhargavaGunnam-MSFT

don't have issues with data, its not working for file to table load and tested for table to table load also

in azure sql database using primary key column , getting the same error.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-05-24T22:08:44.6266667+00:00

Hello Rajesh,Rome Lin

Can you please check the below thread and see if it helps.

https://learn.microsoft.com/en-us/answers/questions/725402/adf-copy-data-activity-(with-upsert)-fails-when-th

Accepted answer

0 additional answers

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-05-22T22:12:41.19+00:00

Hello Rajesh Gangam,

Welcome to the Microsoft Q&A forum

This seems like a data issue.

Can you please check the upsert key column and make sure the key column doesnot contain the nulls or duplicate values?
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-05-23T21:09:11.9666667+00:00

Hello Rajesh Gangam,

I am checking to see if you had a chance to look into my above response.
Rome Lin 10 Reputation points

2024-05-23T22:16:51.7166667+00:00

Hi BhargavaGunnam-MSFT,

I also ran into a similar issue with Azure Data Factory Upsert with the error message as follows:

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

I created a table first in Azure SQL database with a primary key and used this primary key for my upsert key column in Azure Data Factory. Somehow, it just will not work. Please take a look, I appreciate your feedback. Thanks
Rajesh 45 Reputation points

2024-05-23T23:28:09.6266667+00:00

Hi BhargavaGunnam-MSFT

don't have issues with data, its not working for file to table load and tested for table to table load also

in azure sql database using primary key column , getting the same error.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-05-24T22:08:44.6266667+00:00

Hello Rajesh,Rome Lin

Can you please check the below thread and see if it helps.

https://learn.microsoft.com/en-us/answers/questions/725402/adf-copy-data-activity-(with-upsert)-fails-when-th

Answer 1

Omokhoa Agbagbara 95

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

Create a table in sql server
In the table say "timesheets", have a unique index eg timesheetId.
In the pipeline, import schemas, and perform all the required mappings
In the sink specify the timesheetId for the key, for upsert.
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.

Omokhoa Agbagbara 95 Reputation points

2024-06-06T05:45:31.9266667+00:00

I think I found what my problem was, Just in case you ever come across the problem
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-06-06T18:59:51.0233333+00:00

Thanks Omokhoa for sharing this.

Hello Rajesh,Rome Lin,

Please check the above solution and see if it resolves your issue.
Rajesh 45 Reputation points

2024-06-07T01:09:34.28+00:00

Thanks Omokhoa Agbagbara ,BhargavaGunnam-MSFT , it resolves my issue
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-06-07T15:08:14.96+00:00

Thanks Rajesh

Please consider accept Omokhoa Agbagbar's answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.
Kai Hartmann 0 Reputation points

2024-08-05T11:32:27.2733333+00:00

It would also be helpful, if the accepted answer would contain a hint, that the actual solution is contained in the comment below that answer. ;)

Share via

getting error in copy data activity while using upsert

0 additional answers

Your answer