How to retrieve json data from API and store it in Azure SQL using Azure automation

Today I am going to show you easy way how to retrieve data from the REST API and store it in Azure SQL. For this exercise I am going to use the following site https://jsonplaceholder.typicode.com/todos with the following format of the json:

  {
    "userId": 1,
    "id": 1,
    "title": "delectus aut autem",
    "completed": false
  },

I leave it with you to get Azure SQL sorted as there are more than enough examples how to get it done.

So first of all, we assign the values to variables that will describe our setup.

 

$SqlUsername = 'myusername' 
$SqlPass = 'SecretPass234'
$SqlServer = 'apidbdemo333.database.windows.net'
$SqlServerPort = 1433
$Database = 'API'
$Table = 'Data'
[string]$webstring = "https://jsonplaceholder.typicode.com/todos"

Then we create web client and sql objects assigning it powershell variable. The rest of the powershell is as per below

$webget = New-Object System.Net.WebClient
$result = $webget.DownloadString($webstring)
$result = $result | ConvertFrom-Json

$SQLconnString = "Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $SQLconnString
$conn.open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn

foreach ($row in $result)
{
  $cmd.commandtext = "INSERT INTO $Table (UserID, ID, Title, Body) VALUES ( '{0}', '{1}','{2}','{3}')" -f $row.userID, $row.id, $row.title, $row.completed
  $cmd.ExecuteScalar()
}
$conn.close()

$SQLread = "SELECT * FROM $Table"
$SQLarray = @()

try
{
    $conn = New-Object System.Data.SqlClient.SQLConnection($SQLconnString)
    $conn.open()
    $cmd = New-Object system.Data.SqlClient.SqlCommand($sqlread, $conn)
    $reader = $cmd.ExecuteReader()
    $Counter = $Reader.FieldCount
    while ($Reader.Read())
    {
        $tuple = @{ }
        for ($i = 0; $i -lt $Counter; $i++)
        {
            $tuple."$($Reader.GetName($i))" = $Reader.GetValue($i)
        }
        $SQLarray += $tuple
    }
    $SQLarray | convertTo-JSON
}
catch
{
    $ex = $_.Exception
    Write-Error "whilst opening source $SqlServer . $Database . $Table : $ex.Message"
}
finally
{
    $reader.close()
}

Happy days!

One comment

Leave a Reply

Your email address will not be published.