At the time of writing there isn't a SQL Server Integration Services (SSIS) task that will collect JSON or XML formatted data directly from an API call. There are various add-ons and products that are available however, it is straightforward to achieve this using PowerShell and since you are likely to use PowerShell to automate other tasks on Microsoft platforms, kicking off PowerShell scripts in SSIS is a useful design pattern that can often be reused (see my earlier post on this subject).
Back in 2016 I gave an example to directly pull some XML weather data from an API and directly populated a table in SQL server. In this example I am going to focus on JSON formatted data, which I see much more of in my domain.
In this example, I'm going to use the publicly available from the UK Office Of National Statistics (ONS) figures to gather some economic data, the "Retail Price All Items Index" which has the identifier CHAW. I like the design of the ONS site, by adding "/data" to the url you can directly access the JSON data.
- https://www.ons.gov.uk/economy/inflationandpriceindices/timeseries/CHAW - webpage
- https://www.ons.gov.uk/economy/inflationandpriceindices/timeseries/CHAW/data - JSON data
This is an easy grab in PowerShell.
Use the Invoke-Restmethod to get the well formed JSON data into the $Results object. The entire dataset is now available in the $Results object. Create a custom object type (in this case $ONSData) to hold the information that you are interested in. In this simple example you can see that there are only two items of interest and creating another object for the date and the value is excessive, but imagine a more complicated scenario. Having this intermediate step of constructing and populating the $ONSData object will isolate the data extraction from the $Results object to just one section of code.
And that is it, just dump it to a writable file location (in this case C:\Temp\ONS_Economic_Data.csv on my SSIS server) and you can pick up the data file in the next SSIS task.
Chatting with colleagues, I agree that you can write a script task in SSIS to do the same and pass the data to the next SSIS step from memory without the need to write to the file system which is far quicker and more elegant. In my scenario, my colleagues had some PowerShell experience and little SSIS and C# experience - so this more practical than efficient. I'll present the script task solution in a future post.