HTTP Linked Service - CSV file not coming through

Question

Hi all,

I'm running into a weird situation trying to pull data from a csv file, using the HTTP linked service, and place it in an Azure SQL Database. This has been running fine until last Friday but since then, it appears the file is read but no data is copied.

I'm pulling data from the Nomis API which is publicly available which makes it easier to share here the details of what I'm trying to do.

Linked Service Definition

BaseUrl - http://www.nomisweb.co.uk/api/v01/dataset/

Dataset Definition

Delimited Text File

RelativeUrl - NM_162_1.data.csv?geography=1811939640...1811939647&date=latest&gender=0&age=0&measure=1&measures=20100&select=date_name,geography_name,geography_code,obs_value

Pipeline Definition

The pipeline has a single Copy data activity where the dataset described above is used as the source with a GET request method and the sink is an Azure SQL Database.

As I mentioned, until last Friday, this would be the outcome of running this pipeline

As of Friday onwards, it says 1 file is read but 0 bytes of data are read

Nothing has changed in the pipeline, datasets or linked services definitions. The data itself is still accessible which can be easily checked by opening a browser window and entering the base url + relative url (a csv file with 8 records will be downloaded).

Any idea of what may be happening here?

Thanks
Pedro

Answer 1

Hi @Pedro Fiadeiro ,

Thankyou for using Microsoft Q&A platform and posting your query.

As per my understanding about your query, it looks like you are trying to fetch data from API and load it into Az SQL table using HTTP (.csv connector) dataset as source in Copy activity . The pipeline was untouched but all of a sudden started giving different result than expected- No rows getting copied. Please let me know if my understanding has any gap.

I tried to reproduce your scenario and found out that there are two things that got changed probably from the Source API side:

1. API got changed to a secured one , leading us to change http to https in the Base URL of linked service.
2. The relative URL which you are trying to hit is no longed present in the API. Try updating relative URL to NM_1_1.data.csv?geography=2038432081

Here is a short video describing it's working when I made the needed changes:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators