Inferring: Data type from Mapping cannot be determined if the first value of a field is null.

Question

Hello,

so i was wondering if there is a possibility to infer data types for the mapping with a recursive method, instead of just looking at the first row. I will try to explain my usecase as detailed as possible:
I downloaded a Database as a json (Zendesk) and uploaded the json into a blob storage and now i want to use the json file as source for a table in azure sql database. The problem is some of the values in the first row/entry have null as value and thus i have to look into the data and search for an row/entry where that field is not null (empty) to determine the datatype and then manually map that data type in ADF. This is a tedious task since I have to push multiple tables to the azure sql as we are currently migrating in our company.

The only related and relevant threads i found are:
https://learn.microsoft.com/en-us/answers/questions/86880/pipeline-failed-when-parsing-json-due-to-null-valu.html

But this is currently not a viable solution. As im using the LinkedService (SQL Server) for all tables and dont even have a table in sql to map the linked service to.

Best regards

MBW

Answer 1

Hi @MBW ,

Could you please help me understand your ask better by sharing details on below asks.

• Did you tried creating dataset in ADF for your data? If yes, what is the format and connector type of that dataset?
• Do you mean you are trying to create a dataset for SQL table and you don't see proper datatype coming up?

between, You can have your mappings dynamic also in copy activity. Please check below video for better idea if that helps.
https://www.youtube.com/watch?v=b27gmOufge4

You can also import schema in to your datasets using sample files from local.

Answer 2

Thank you By mapping all fields to strings and analyzing the rows in DBeaver, I was able to solve the problem.