I have a working ADF workflow copying the data from GET API call using For Each loop changing the query string each time based on the lookup JSON file and saving separate files to a BLOB storage as JSON files. I have a question - is it possible to load this data into SQL table with a structure of (id, timestamp, filename, json) which means storing each API call result in this table in a new sperate row? I have a problem with mapping the fields to the SQL final table as I can't use simple item().File or get contents of the JSON file that is now stored in container.
7
-
Do you mean that you want to copy the json data from an API and store the data into a SQL table?Joseph Xu– Joseph Xu2020-10-07 07:29:12 +00:00Commented Oct 7, 2020 at 7:29
-
Yes, that's pretty much what I want to achieve - story it to have some kind of history table in this case.Martek– Martek2020-10-07 08:11:33 +00:00Commented Oct 7, 2020 at 8:11
-
Is this documnet helpul to you?Joseph Xu– Joseph Xu2020-10-07 10:24:55 +00:00Commented Oct 7, 2020 at 10:24
-
Sure, it's the standard way that works fine. But I tried to make it the other way - instead of storing a file on Storage Account I was wondering if it's possible to use SQL table as a sink and insert the contents of the file into this table.Martek– Martek2020-10-07 14:07:42 +00:00Commented Oct 7, 2020 at 14:07
-
Where is the filename from? Do you want copy the json into one column in SQL table?Joseph Xu– Joseph Xu2020-10-08 09:15:17 +00:00Commented Oct 8, 2020 at 9:15
|
Show 2 more comments
1 Answer
You can use Stored Procedure activity to sink the json object into one column.
I made a simple test here:
1.I use Lookup activity to get a json array from a rest api.
- I create sql table and stored procedure in Azure SQL:
create table dbo.Logs (
_id bigint primary key IDENTITY(1,1),
log nvarchar(max)
);
--Strored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpsertLogs]
@logs nvarchar(max)
AS
BEGIN
INSERT INTO dbo.Logs (log) values(@logs)
END
3.Then I set the Stroed procedure activity, specify the name and import parameters of the Stroed procedure, use expression @string(activity('Lookup1').output.value) to convert the json array to String type.
4.Run debug, the json array will be copied into one column in the sql table. The result shows:
Hope my answer is helpful for you. Store JSON documents in SQL Server, you can reference here.
1 Comment
Bassist
I expected so much more from ADF. In my case the goal was to store the related table values (customer->customerAddresses). I've ended up with the same approach where SProc was doing all the fancy stuff (extract SQL data and transform it to JSON).