JSON Source in SSIS - adding HTTP Parameters to url to specific exact address

Topics: Example / Documentation, Example needed
Nov 10, 2016 at 10:09 AM
HI

Experienced in SQL but new (this week) to JSON.

Looking to use freely available UK police data for analysis purposes.

Can extract JSON source of police neighbourhoods - https://data.police.uk/api/west-yorkshire/neighbourhoods - giving 36 neghbourhoods with fields [id], [name].

Specific neighbourhood info is then available through https://data.police.uk/api/west-yorkshire/[id] e.g. https://data.police.uk/api/west-yorkshire/KDT_BS - so looking to take 36 versions of this.

I cannot workout how to specific the individual [id] in the JSON Source interface for this url - https://data.police.uk/api/west-yorkshire/[id].

Please could some help me use the HTTP parameter / HTTP Header options to get 1 neighbourhood back please?

Thanks and much appreciated

MattS
Coordinator
Nov 24, 2016 at 2:59 PM
Hi,

sorry for the late reply. You question is legit and absolutely non-trivial. In fact, this feature is not yet supported, although you might get something close by using variables. Given the importance of such a feature, I'll implement this already in the next version of the component. Sorry for the inconvenience.

Regards,
Alberto.
Coordinator
Nov 26, 2016 at 1:21 AM
Edited Nov 26, 2016 at 1:21 AM
Hi,

I'm glad to announce I was able to implement such a critical features today. I've used your case as test case, since it is simple enough to check the basics are ok.
It has been a rush and the code might still contain many bugs, so I am not publishing the new version right away. Instead, I will implement error handling first and then I will release both the deatures in the next update, which will come in one or two weeks.

As a proof of what I've done, here it is a simple screenshot, that should make you happy.

screnn.png

Stay tuned !
Coordinator
Nov 26, 2016 at 1:22 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.