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

MBW 21 Reputation points
2022-04-05T09:13:25.747+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,099 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,915 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
505 questions
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 38,306 Reputation points Microsoft Employee
    2022-04-06T16:41:16.297+00:00

    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.
    190614-image.png


1 additional answer

Sort by: Most helpful
  1. Main Khalifa 1 Reputation point
    2022-05-15T17:55:12.163+00:00

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

    0 comments No comments