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

Dipo Onibile 0 Reputation points
2024-05-30T07:08:10.6766667+00:00

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,542 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,868 questions
{count} votes

2 answers

Sort by: Most helpful
  1. phemanth 7,190 Reputation points Microsoft Vendor
    2024-05-30T09:11:48.8933333+00:00

    @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:

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

    0 comments No comments

  2. rn 0 Reputation points
    2024-06-03T14:13:41.7366667+00:00

    Hello!

    I have a similar issue after updating to 10.0.39.

    However, in my case the OptionsetMedata is empty - hence the error:

    User's image

    Any suggestions? Or is it the expected behaviour?