Posting data to a business central table with Azure Data Factory

Question

Hello I am getting this error

Job failed due to reason: Error response from server: Some({"error":{"code":"Internal_EntityWithSameKeyExists","message":"The record in table PurchaseLinesInvoicesTempTable already exists. Identification fields and values: No.='0' CorrelationId: 4e62b673-23ff-437d-bb0c-288e039da834."}}), Status code: 400. request body: Some({"@No.":65710,"@Description/Comment":"Industrial Thermal Transfer Labels - 1 1⁄2 x 1", Ribbons Required (S-6247)","Quantity":144,"@Department Code":900}), request method: POST)

How can I clear the table in my sandbox before posting and, do I post to the web services API OData endpoint then load a separate page from my sandbox, after I run my pipeline, I just see zero values in the table and I get the above error message. I also wanted to ask, that is this the right way to pass special characters to an Odata endpoint, because for some reason, I can only see the "quantity" value, How do I properly define that? Source is from blob storage, used lookup transformation, and will I be needing an external call transformation to post data to business central using odata web service url?

This is how I renamed it initially and above downstream, the reason I left it as is, is because the final table has that structure. Please advise.

Answer 1

Hi @Kayode Ogidan
Welcome to Microsoft Q&A platform and thanks for posting your question here.

To resolve the issue of receiving an error about a duplicate key when inserting data into a table in Business Central using Azure Data Factory, you can clear the table in your sandbox environment before posting new data.

One way to do this is to use the ‘clean’ mode of extension sync in PowerShell or delete records from the table using the Table Data Editor app in Business Central. If you need to refresh the entire sandbox environment, you can delete the sandbox and create a new one.

You do not necessarily need an external call transformation to post data to Business Central using an OData web service URL. You can write to the Business Central database using an OData web service that exposes a writable page. Ensure that the OData web service is correctly set up and that you have the necessary permissions to write data to the database.

To pass special characters to an OData endpoint, you can convert them into their equivalent characters and properly encode them in the URL. For example, you can remove the ‘\u’ from the column and convert the resultant hexadecimal value into an integer, then use the nchar() function to convert this integer data into a character.

only the first row showing up in the destination table, check the data flow mappings to ensure that all fields are correctly mapped from the source to the destination. Verify that transformations are not filtering out rows or altering data in a way that prevents subsequent rows from being processed. Check the batch size setting in Azure Data Factory to ensure that it's not set to one. Confirm that the data types in the data flow match those expected by Business Central.

please test your changes in the sandbox environment before applying them to production to ensure that the data is posted correctly and that the issue with special characters is resolved.

Reference:
https://stackoverflow.com/questions/4229054/how-are-special-characters-handled-in-an-odata-query

https://learn.microsoft.com/en-us/azure/data-factory/connector-odata?tabs=data-factory
https://github.com/Microsoft/AL/issues/3479

https://navinsights.net/2020/10/27/4-ways-to-delete-records-from-a-table/

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-odata-to-modify-data

https://www.sikich.com/insight/how-to-refresh-a-business-central-sandbox-environment-and-copy-a-company/

I hope this information helps you. Let me know if you have any further questions or concerns.