0

I'm trying to ingest the Json array format from US Census API data into Azure either ASA or ADF is fine... Tried using HTTP or REST and none of them is successful.enter image description here

The error after using HTTP connector is shown as

"Error occurred when deserializing source JSON file 'c753cdb5-b33b-4f22-9ca2-778c97a69953'. Check if the data is in valid JSON object format. Error reading JObject from JsonReader. Current JsonReader item is not an object: StartArray. Path '[0]', line 1, position 2. Activity ID: 8608038f-3dd1-474f-86f1-d94bf5a45eba".

I attached the error message as well as sample API data and "test connection successful" screenshots in this post.

Shall I put in some parameters or advanced set up to specify something about the array form for the census data? Please advise.

The sample data link is inserted for your reference.

https://api.census.gov/data/2020/acs/acs5/subject?get=group(S1903)&for=state:51

Greatly appreciate your help in advance!

T.R. error in azure synapse ingestion

connection test is good

US Census API Sample Test Data

Abhishek K
  • 3,047
  • 1
  • 6
  • 19
azure rr2
  • 1
  • 1

1 Answers1

0

As per official documentation As REST connector only support response in JSON.

enter image description here

Data link provided by you returns json array not Json. That is why ADF can not accept data returned by data link.

Abhishek K
  • 3,047
  • 1
  • 6
  • 19
  • Thank you for checking on this. So there is no way to ingest directly via REST or HTTP? The error message screen I got actually is from HTTP. Could you share your suggestions about how to do this in a productive way? I have lots of APIs in similar format and I don't want to download COPY each one of them to Datalake... Thank you SO MUCH in advance! – azure rr2 Jul 21 '22 at 18:26
  • You can refer to this similar Q&A thread( https://learn.microsoft.com/en-us/answers/questions/754949/azure-data-factory-copy-activity-json-array-with-d.html ), to deal with json array in ADF. – Abhishek K Jul 22 '22 at 07:36