CODEWITHSUNDEEP
jsonazureazure-data-factory
Martek
Martek

Reputation: 23

Azure Data Factory Copy JSON to a row in a table

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.

Upvotes: 0

Views: 4215

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6083

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.

enter image description here

  1. 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.

enter image description here

4.Run debug, the json array will be copied into one column in the sql table. The result shows:

enter image description here

Hope my answer is helpful for you. Store JSON documents in SQL Server, you can reference here.

Upvotes: 2

Related Questions