question

LINTOVINCENT-4517 avatar image
0 Votes"
LINTOVINCENT-4517 asked AnnuKumari-MSFT commented

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

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PratikSomaiya avatar image
0 Votes"
PratikSomaiya answered AnnuKumari-MSFT commented

Hello @LINTOVINCENT-4517

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @PratikSomaiya , Thanks for the update.

In my case I am not facing the null case for the records, its for the entire attribute.

For example my fetch xml is below

<fetch >
<entity name='datastore' >
<attribute name="store_id" />
<attribute name="modifiedon" />
<attribute name="store_location" />
<attribute name="store_name" />
<filter>
<condition
attribute="modifiedon"
operator="gt"
value="2022-04-01"/>
</filter>
</entity>
</fetch>


My CRM entity looks like

194580-crm-entity.jpg




So after the execution's ,I am only able to see two rows records without 'store_location' column due to the null value for two records.


So that I am checking for any other option to add dynamic column to the source of copy data activity if the the attribute not present in the fetchxml query.

A related question already raised in the below link

https://docs.microsoft.com/en-us/answers/questions/793295/how-to-get-all-the-fields-listed-there-in-crm-usin.html

0 Votes 0 ·
crm-entity.jpg (35.2 KiB)
PratikSomaiya avatar image PratikSomaiya LINTOVINCENT-4517 ·

I think in ADF Data Flow you can use a feature to replace Null values, can you check if D365 is supported as a source in Data Flows?

0 Votes 0 ·

Hi @LINTOVINCENT-4517 ,
Just checking in to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer and kindly upvote the same. If you have extra questions about this answer, please do Comment.

0 Votes 0 ·
Show more comments