Search code examples
azure-data-factory

Azure Data Factory Link Connection to REST API Error when deserializing source JSON file

I have setup both a REST API and HTTP Link service to our APIs with ADF.

The objective is to copy data from our API to one of our SQLDBs

The link connection is successful, however, when I attempt to preview the data I get the following error with HTTP Link Connection:

enter image description here

With the REST API link connection I get the following error:

enter image description here

The HTTP Configuration is as follows:

enter image description here

enter image description here

enter image description here

The REST API configuration is as follows: enter image description here

enter image description here

enter image description here

The format of the data is as follows:

enter image description here

Can someone let me know why I'm getting the error regarding the JSON? As you can see the JSON format looks fine to me.

I've updated the question show the format of the data in it's raw format, see below image

enter image description here

I also tried by entering the full Base URL enter image description here

Solution 
  • Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format.  
Unexpected character encountered while parsing value: <. Path '', line 0, position 0.

    The REST dataset expects JSON format to be returned. JSON starts with { or [. The returned value started with <. HTML and XML start with <. I suspect the result was an HTML page instead of raw JSON data. So, if you forgot to authenticate or put the wrong password, then the Copy activity might be redirected to an error page or login page. These pages are in HTML format, and so start with <. You can follow the procedure below to get JSON from a REST API using a web activity as follows:

    enter image description here

    You will get the JSON as output of the web activity. Store the output into a variable using the set variable activity with the @json(activity('Web1').output.Response) expression as shown below:

    enter image description here

    By using the copy activity, copy that variable value into a blob storage account as follows. Select a CSV file which has one column and one row as the source, and add the variable value as an additional column using the @string(variables('url')) expression as shown below:

    enter image description here

    Select another delimited text dataset with the requirements below as sink:

    enter image description here

    Add mapping as shown below:

    enter image description here

    Debug the pipeline, and it will run successfully as shown below:

    enter image description here

    The JSON will copy into the storage account successfully. Use the copy activity or data flow according to the JSON structure to copy into the required sink.