Simplest way to call REST api and insert resulting json data into SQL Server

Question

I have a bunch of REST API calls that return flat-ish json data that I want to call periodically and dump into a SQL Server database. What's a simple way to do this without writing lots of code? I'm on a windows platform and happy using C#, Powershell, T-SQL, SSIS, or something else 'normal' for windows platforms.

I'd prefer not to have to hand-code anything based on the json fields, i.e. it'd be great if it can automatically look at the json and create destination tables in SQL Server or at least do the mapping mostly for me.

Generally my API results are an array of similar-looking json objects with no nested objects. Different API calls have json objects with different fields, but results of any one call all have the same fields, e.g.

[ 
    {
      "id": "abc",
      "quantity": 1234,
      "price": 3456.03,
      "entityId": 99,
      "anotherField": "cows"
    },
...
]

Answer 1

My favourite solution for this was to use Powershell Invoke-RestMethod and Write-SqlTableData, as it avoids either creating the SQL tables ahead of time or writing anything field- or column-specific. Also use Invoke-Sqlcmd if you want to do other SQL things, e.g. clear the staging table ahead of each import.

$url = 'https://jsonplaceholder.typicode.com/todos'

# Add any headers needed, e.g. Authorization headers
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/json")

# call the REST api and get results as an array of objects
$response = Invoke-RestMethod $url-Method 'GET' -Headers $headers    

# clear out the table ahead of each load. Use 'drop table' instead if the schema might change.
Invoke-Sqlcmd -Query "if object_id('dbo.import_todos', 'U') is not null begin truncate table dbo.import_todos; end" -ServerInstance . -Database 'scratch'

# Dump the api results into a DB table. This will create the table if it doesn't already exist.
# To use this cmdlet first install it using: Install-Module SQLServer
Write-SqlTableData -ServerInstance '.' -DatabaseName 'scratch' -SchemaName "dbo" `
                   -TableName "import_todos" -InputData $response -Force

The articles here and here were super helpful.