This is the second challenge:
+ Periodically get currency exchange rates from a web service
+ Insert the rows in a staging table (SQL Database On-premise)
+ Parse inserted Json Data with a stored procedure and insert results in our final Currency Exchange Rates Table.
The resulting Flow would look like this:
Let’s get things started:
HTTP: I’m using fixer.io to get my data. No Problem so far.
Insert Row: I have installed and configured the On-premises data gateway and it’s working.
Execute stored procedure: Here comes trouble:
Sadly up to now stored procedures are no supported when using a gateway. But hopefully we get this feature soon as stated here.
I then figured out the following workaround. Create an AfterInsert Trigger on the staging table to transfer my data to the final table. But I had now luck:
After some investigation, I found this thread. Currently you can’t use the Insert Row action on a table with an existing trigger.
So, this is really bad. At present neither stored procedures nor triggers are supported when dealing with Microsoft SQL Databases via the On-premises data gateway. Challenge lost at least for the moment.