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
deanmachine333Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
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?
0
Pawan KumarDatabase ExpertCommented:
You can create a stored procedure

and call the stored procedure using SQL Job with 1 minute interval.
0
deanmachine333Author 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/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Pawan KumarDatabase ExpertCommented:
SQL Server Proc will be the fastest. SSIS will also internally call SQL only.
0
deanmachine333Author Commented:
@Pawan hiya, can you advise using the example API how the code would be achieved, please?
0
Pawan KumarDatabase ExpertCommented:
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/
0
deanmachine333Author Commented:
@Pawan can you advise the sql code to achieve this please?
0
Ryan ChongCommented:
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/
0
Pawan KumarDatabase ExpertCommented:
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

0
Daniel_PLDB Expert/ArchitectCommented:
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
0
David Johnson, CD, MVPOwnerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deanmachine333Author 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?
0
David Johnson, CD, MVPOwnerCommented:
0
deanmachine333Author Commented:
thanks got it working with Davids solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.