Copy activity failing at sink having upsert

Question

Copy activity failing at sink having upsert

Jitender Kumar Chandel 21

I am working on copy activity in ADF having sink as Azure SQL DB and upsert as an option.

It's failing with below error:

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 have checked and the column exists at the source and sink. There are no null values at the source or sink.

"sink": { "type": "AzureSqlSink", "writeBehavior": "upsert", "upsertSettings": { "useTempDB": true, "keys": [ "Id" ] }, "sqlWriterUseTableLock": false, "disableMetricsCollection": false }, "enableStaging": false, "translator": { "type": "TabularTranslator", "typeConversion": true, "typeConversionSettings": { "allowDataTruncation": true, "treatBooleanAsNumber": false } } }

executionDetails": [ { "source": { "type": "Salesforce" }, "sink": { "type": "AzureSqlDatabase" }, "status": "Failed", "start": "8/6/2024, 12:17:35 PM", "duration": 15, "usedParallelCopies": 1, "profile": { "queue": { "status": "Completed", "duration": 4 }, "transfer": { "status": "Completed", "duration": 9, "details": { "readingFromSource": { "type": "Salesforce", "workingDuration": 0, "timeToFirstByte": 3 }, "writingToInterim": { "type": "AzureSqlDatabase", "workingDuration": 0 }, "writingToSink": { "type": "AzureSqlDatabase", "workingDuration": 0 } } } },

User's image

Accepted answer

0 additional answers

Your answer

Answer 1

Smaran Thoomu 21,505 Microsoft External Staff

Hi @Jitender Kumar Chandel

Thanks for the question and using MS Q&A platform.

Based on the error message you provided, it seems that there is an issue with the sink side of the copy activity. The error message indicates a null reference exception, which means the code is trying to access an object that is null. I believe the issue might be related to setting the linked service of the sink dataset to Legacy. Please check this and let us know if it resolves the issue.
User's image

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Jitender Kumar Chandel 21 Reputation points

2024-08-06T11:26:22.6866667+00:00

Changing the linked service to legacy is giving the below error. It may be because of self-hosted integration runtime.

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'INCORPINDB1', Database: 'ADF_Test', User: 'Nagarro_user'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.), SqlErrorNumber=-2146893019,Class=20,State=0,

The certificate chain was issued by an authority that is not trusted
Smaran Thoomu 21,505 Reputation points Microsoft External Staff

2024-08-06T12:37:24.27+00:00

@Jitender Kumar Chandel It looks like you're running into a certificate trust issue when trying to connect to your SQL database using a self-hosted integration runtime.

The error message indicates that the certificate chain presented by the SQL server is not trusted by the integration runtime. This is likely because the SQL server's certificate is not issued by a trusted certificate authority (CA) or the intermediate certificates are not properly configured.
Please refer this: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/certificate-chain-not-trusted?tabs=ole-db-driver-19

I hope this information is helpful. Let me know if you have any further questions.
Smaran Thoomu 21,505 Reputation points Microsoft External Staff

2024-08-08T12:25:52.0233333+00:00

@Jitender Kumar Chandel Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Jitender Kumar Chandel 21 Reputation points

2024-08-08T13:38:57.33+00:00

Yes; It did somewhat give me idea to tackle it.
I added Additional connection properties
trustservercertificate = True
and it worked.

Thanks for your support and response.
Smaran Thoomu 21,505 Reputation points Microsoft External Staff

2024-08-09T11:42:17.44+00:00

Jitender Kumar Chandel Glad to know that your issue has been resolved. please make this as accepted answer. Marking answers as accepted helps community to resolve their issues as well. Thank you.
Smaran Thoomu 21,505 Reputation points Microsoft External Staff

2024-08-12T06:40:43.9133333+00:00

Jitender Kumar Chandel Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Copy activity failing at sink having upsert

0 additional answers

Your answer