We help IT Professionals succeed at work.

Import Web API into sql server table every minute easily

Hello ,

Im trying to find a quick easy way to import the results from a Web API into a sql server database , which will run every minute?

the example of the API is the following

https://bittrex.com/api/v1.1/public/getmarketsummaries   

Can anyone advise on this please?

Thanks
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
what version of MS SQL are you using?

are you consider to use SSIS or a custom ETL program to import the data to MS SQL?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can create a stored procedure

and call the stored procedure using SQL Job with 1 minute interval.

Author

Commented:
Hello Ryan,

I prop be using SQL Server 2016 and i could use SSIS , im just trying to see what the quickest and easiest way to get the output from the API into a sql server table.

Ive seen this product online which is what im looking to achieve
https://api-db.com/
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
SQL Server Proc will be the fastest. SSIS will also internally call SQL only.

Author

Commented:
@Pawan hiya, can you advise using the example API how the code would be achieved, please?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
In fact you can the web API directly from SQL Server itself. See if this works for you https://www.mssqltips.com/sqlservertip/3495/extracting-api-data-using-powershell-and-loading-into-sql-server/

Author

Commented:
@Pawan can you advise the sql code to achieve this please?
Ryan ChongSoftware Team Lead

Commented:
you may customize and do some test from the methods mentioned in this article:

Consuming JSON Formatted API Data in 2016
http://www.sqlservercentral.com/articles/JSON/141175/
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
sample code for you -

CREATE PROC CallWebService
(
	 @strSerialNumber VARCHAR(50)
	,@strPassword VARCHAR(50)
	,@strUserID VARCHAR(50)
	,@strPostcode VARCHAR(50)
	,@strProperty VARCHAR(50)
	,@strCountyType VARCHAR(50)
)
AS
BEGIN

	Declare @Ob as Int, @ReturnData as Varchar(MAX);
	DECLARE @WebU AS VARCHAR(MAX) = 'http://ws.afd.co.uk/PCE_WebService.asmx/AddressList?strSerialNumber'
	SET @WebU = @WebU + @strSerialNumber + '&strPassword=' + @strPassword + '&strUserID=' + @strUserID + '&strPostcode=' + @strPostcode + '&strProperty=' + @strProperty + '&strCountyType=' + @strCountyType
	Exec sp_OACreate 'MSXML2.XMLHTTP', @Ob OUT;
	Exec sp_OAMethod @Ob, 'open', NULL, 'get', @WebU,'false'
	Exec sp_OAMethod @Ob, 'send'
	Exec sp_OAMethod @Ob, 'responseText', @ReturnData OUTPUT
	Select @ReturnData
	Exec sp_OADestroy @Ob

END

Open in new window

Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

If you want to use code from inside SQL Server by using Ole Automation you need to enable it first, by default it is disabled.
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO  

Open in new window


However, I suggest to use Powershell type of SQL Server Agent Job. I find it more stable and secure than going out with Ole Automation.

Regards,
Daniel
Distinguished Expert 2019
Commented:
powershell script
#requires -version 3.0

function Add-APIData
{
  <#
    .SYNOPSIS
    Describe purpose of "Add-APIData" in 1-2 sentences.

    .DESCRIPTION
    Add a more complete description of what the function does.

    .PARAMETER server
    Describe parameter -server.

    .PARAMETER database
    Describe parameter -database.

    .PARAMETER text
    Describe parameter -text.

    .EXAMPLE
    Add-APIData -server Value -database Value -text Value
    Describe what this call does

    .NOTES
    Place additional notes here.

    .LINK
    URLs to related sites
    The first link is opened by Get-Help -Online Add-APIData

    .INPUTS
    List of input types that are accepted by this function.

    .OUTPUTS
    List of output types produced by this function.
  #>


  
  param
  (
    [Parameter(Mandatory=$true,HelpMessage='Add help message for user')]
    [Object]$server,

    [Parameter(Mandatory=$true,HelpMessage='Add help message for user')]
    [Object]$database,

    [Parameter(Mandatory=$true,HelpMessage='Add help message for user')]
    [Object]$text
  )
$scon                    = New-Object -TypeName System.Data.SqlClient.SqlConnection
  $scon.ConnectionString = ('SERVER={0};DATABASE={1};Integrated Security=true' -f $server, $database)
  $cmd                   = New-Object -TypeName System.Data.SqlClient.SqlCommand
  $cmd.Connection        = $scon
  $cmd.CommandText       = $text
  $cmd.CommandTimeout    = 0
$scon.Open()
  $cmd.ExecuteNonQuery()
  $scon.Close()
  $cmd.Dispose()
  $scon.Dispose()
}


$nl   = "`n"

$json = (Invoke-WebRequest -Uri https://bittrex.com/api/v1.1/public/getmarketsummaries ) -join "`n" | ConvertFrom-Json
$add  = @()
foreach ($r in $json.result)
{
  $add += "INSERT INTO tb_FRED_WTI VALUES ('" + $r.MarketName + "','" + $r.High + "','" + $r.Low + "','" + $r.Volume + "','" +`
  $r.Last + "','" + $r.Volume + "','" + $r.Last + "','" + $r.BaseVolume + "','" + $r.TimeStamp + "','" + $r.Bid + "','" +`
  $r.Ask +  "','" + $r.OpenBuyOrders + "','" + $r.OpenSellOrders + "','" + $r.PrevDay + "','" + $r.Created  + "')" + $nl
}
Add-APIData -server 'TIMOTHY\SQLEXPRESS' -database 'MSSQLTips' -text $add

Open in new window


original source: https://www.mssqltips.com/sqlservertip/3495/extracting-api-data-using-powershell-and-loading-into-sql-server/
updated to use data from Question

Author

Commented:
@david thanks i got it working with that code ( few mods but working huge thanks ) what would you advise for me to run the powershell script every minute?
Distinguished Expert 2019
Commented:

Author

Commented:
thanks got it working with Davids solution.