How to fix {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'peopleSQL': ERRORCODE:MSSQL_101, STAGE:UPSERT-N-WAY, SQLERRORCODE:1750, MESSAGE:Could not create constraint or index.

Question

Hello,

I struggle with running a pipeline which calls a data flow. At the end of the flow the data should get updated (via Upsert) into a SQL database. But I get the following error and I have no idea what could be wrong and where to look for to solve it.

"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'peopleSQL': ERRORCODE:MSSQL_101, STAGE:UPSERT-N-WAY, SQLERRORCODE:1750, MESSAGE:Could not create constraint or index."

Another pipeline which calls a Copy action (copy the data from a csv file via Upsert into the same SQL database) works just fine.

Any hints would be really appreciated. Let me know if you need any specific information.

Thanks and regards,
Roger

Answer 1

@Roger Zirn - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: How to fix {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'peopleSQL': ERRORCODE:MSSQL_101, STAGE:UPSERT-N-WAY, SQLERRORCODE:1750, MESSAGE:Could not create constraint or index.

Solution: The issue is resolved. At the end, the solution is very simple. As I didn't want the key column to get overwritten, I enabled the sink setting "Skip writing key columns". But if there are any new rows to be inserted, the key column must be written. This was my misconception.

That said, I disabled the setting and no more errors are showing up. What a surprise... ;-)

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

@Roger Zirn - Thanks for the question and using MS Q&A platform.

It seems like there is an issue with creating a constraint or index in the SQL database. The error message you provided indicates that the error occurred during the Upsert-N-Way stage of the pipeline.

One possible cause of this error is that the data being upserted violates a constraint or index in the target SQL database. You may want to check if there are any constraints or indexes on the target table that are being violated by the data being upserted.

Another possible cause of this error is that the target SQL database is not configured correctly. You may want to check if the target SQL database has the necessary permissions to create constraints or indexes.

You can also try to run the same Upsert operation manually in SQL Server Management Studio to see if you get the same error. This may help you narrow down the issue.

If you are still having trouble, please provide more information about your pipeline and data flow, such as the schema of the source and target tables, and the configuration of the Upsert operation.

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

Answer 3

At the end, the solution is very simple. As I didn't want the key column to get overwritten, I enabled the sink setting "Skip writing key columns". But if there are any new rows to be inserted, the key column must be written. This was my misconception.

That said, I disabled the setting and no more errors are showing up. What a surprise... ;-)