How to update null values into on premise sql during incremetal load of a copy data activity in Azure Data Factory

LINTO VINCENT 1 Reputation point
2022-04-20T08:35:10.027+00:00

Hi All,

I am trying to copy data from dynamic 365 to on premise sql server with the help of Azure Data Factory. The source of Copy data activity is fetch xml query with required attributes and the sink side write behavior as Upsert. It is working fine for first load.

For the incremental load some of the attributes are missing from the fetch xml response(Expected behavior due to the null values for the attributes ), In such situation upsert not update the value as null for corresponding records in sql, because these attributes are missing from the source.

So how to pass the null value into sink side upsert process? I have opted for additional columns in the source but it creates issue when there is missing attributes available for the next incremental load

Note: All columns field in the sql table accept null

Thanks in Aavance

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,135 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Pratik Somaiya 4,206 Reputation points
    2022-04-20T09:44:25.813+00:00

    Hello @LINTO VINCENT

    You will have to update the Fetch XML to allow NULL values or add a filter to the Fetch XML to allow such values

    A similar question is available in these two links:

    https://stackoverflow.com/questions/62578976/fetchxml-link-entity-including-null-values

    https://community.dynamics.com/crm/b/crmtipsbyprm/posts/work-with-null-parameters-in-dynamics-365-fetch-xml-report