Issue with Copy Dataverse data into Azure SQL using Synapse Link Template

Question

I am experiencing issues using ADF

I am trying to use the template "Copy Dataverse data into Azure SQL using Synapse Link" to move f&o data from datalake to Azure sql

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot insert the value NULL into column 'GlobalOptionSetName', table 'XXXXX.dbo.FO_GlobalOptionSetMetadata'; column does not allow nulls. INSERT fails.

The error occurs while the Copy Data Activity "Copy GlobalOptionsetMetadata" runs.

Any help would be appreciated.

Thanks

Answer 1

@Dipo Onibile

Thanks for using MS Q&A platform and posting your query

The error message indicates that you're trying to insert a NULL value into the GlobalOptionSetName column of the FO_GlobalOptionSetMetadata table in your Azure SQL database, but this column doesn't allow null values.

Here's how to troubleshoot and fix this issue:

Check Dataverse data:

• Verify if the GlobalOptionSetName field in your Dataverse f&o data actually contains null values. You can use Power Apps or the Dataverse web interface to check the data.
• If there are null values, you'll need to address them before copying the data. This could involve:
• Filling the null values with a default value in Dataverse.
• Filtering out rows with null values before copying to Azure SQL.

Modify the ADF Pipeline:

• If null values are expected but the table schema disallows them, you can modify the ADF pipeline to handle nulls:
• Use a data transformation activity to replace null values with a valid value (e.g., an empty string).
• Modify the table schema in Azure SQL to allow nulls for the GlobalOptionSetName column (not recommended as it might affect other processes).

Here are some additional resources that might be helpful:

• Troubleshoot data factory errors: https://learn.microsoft.com/en-us/azure/data-factory/tutorial-pipeline-failure-error-handling
• Copy Activity in Azure Data Factory: https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview
• Modify Data in Azure Data Factory: https://learn.microsoft.com/en-us/azure/data-factory/

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

Answer 2

@Dipo Onibile

Welcome to Microsoft Q&A platform and thanks for posting your question.

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:**I am experiencing issues using ADF

I am trying to use the template "Copy Dataverse data into Azure SQL using Synapse Link" to move f&o data from datalake to Azure sql

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot insert the value NULL into column 'GlobalOptionSetName', table 'XXXXX.dbo.FO_GlobalOptionSetMetadata'; column does not allow nulls. INSERT fails.

The error occurs while the Copy Data Activity "Copy GlobalOptionsetMetadata" runs.

**Solution:**I was able to figure it out. I made some modifications on the Copy Data Activity "Copy GlobalOptionsetMetadata".

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.