Share via

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,597 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,977 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. phemanth 7,825 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.

  2. phemanth 7,825 Reputation points Microsoft Vendor
    2024-06-26T07:04:43.52+00:00

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