CODEWITHSUNDEEP
sql-serverapissisjson-apikingswaysoft
Artem
Artem

Reputation: 833

Request data from JSON API and store in SQL Server using SSIS

I'm trying to request data from a JSON API from Vemcount - a footfall-sensor system. I want to retrieve all my company's data and store it in a SQL Server db using SSIS. During my research I've realized that JSON is not natively integrated with SSIS, so solutions are either hardcoding the import or using a 3rd party - I've chose to go with the latter, so currently I'm trying to do the request using Kingswaysoft and their JSON Source component in VS.

The URL looks something like this (example from their documentation, which can be found here): 

https://login.vemcount.com/api/fetch_data/?data={"api_key":"xxxxxxx","type":"shop_id","id":[10,14,24],"date_from":
"2014-01-01 00:00","interval":"15min"}

So, the keys api_key, type, and id are mandatory. The key/value pairs "type":"shop_id" and "id":[10,14,24] are the type of ID I want to search on and the ID itself. In the above example I've entered 3 different shop_id's. However, we have more than 250 id's, and more are being added continuously.

How do I request ALL the different id's without adding all the id's in the array? I've pasted my HTTP CM and JSON Source Editor below.

As you've probably guessed by now, this is somewhat unexplored territory for me, so I'm aware I might have chosen to proceed in a completely wrong way. If you have any better solutions for my case, please let me know.

UPDATE: So I've stored all Shop ID's in a variable, which I wanna pass as a value.

enter image description here enter image description here enter image description here

Upvotes: 1

Views: 3457

Answers (1)

KeithL
KeithL

Reputation: 5594

I am answering on phone so I don't have ssis available but here are the steps id do.

  1. Exec sql to get the list of shop ids saved into an object.

  2. For each loop on ado object from 1 storing shop id into variable.

  3. Data flow

  4. Script component source passing in shop ID as variable.

  5. Build your url.

  6. Using web client download string.

  7. Deserialize JSON and write to data flow to finish.

Step 7 is huge and requires its own question. It involves setting up classes for your json.

This should get you started. Research step7 before posting question though.

Good luck.

Also, here is a link to help I received in regards to deserializing JSON. C# Checking if Object is Null

Upvotes: 2

Related Questions