Import Web API into sql server table every minute easily

deanmachine333
deanmachine333 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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/
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
Top Expert 2016
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?
Top Expert 2016
Commented:

Author

Commented:
thanks got it working with Davids solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial