Unable to import json array to sql table in azure data factory-CodePudding

I have a JSON array similar to this.

 {
"first":
    {
    "heading":{
    "row":[
    {
    "@captain" :"dhoni",
    "@runs":"50"
    },
    {
    "@captain" :"Kohli",
    "@runs":"150"
    }
    ]
    }
    }
    }

But I am not sure how to add a sql script to import this JSON to my sql table. Can anyone help on this

CodePudding user response：

You can insert the rows extracted from the json returned by your API call with the help of for each activity and script activity.

I have taken the given sample json as a parameter (Object type). First create a for each activity. The value of items in for each activity will be as follows:

@pipeline().parameters.my_json['first']['heading']['row']

When we do this, for each iteration, the current item will be a row extracted from JSON that needs to be inserted into sql table.

enter image description here

Now inside for each, create a script activity. Here, write a query to insert into the destination SQL table. You can use the following as your query

insert into dbo.player(captain,runs) values ('@{item()['@captain']}','@{item()['@runs']}')

enter image description here

Another approach using stored procedure:

Create the following stored procedure in your database.

create  or  alter  procedure insert_player @json varchar(max)
as
begin
insert  into player SELECT  *  FROM OPENJSON(@json,'$.first.heading.row') with ([@captain] varchar(30),[@runs] varchar(30))
end

Create a single stored procedure activity in data factory pipeline. Select the required linked service, the above created stored procedure and click import under stored procedure parameter.

@string(pipeline().parameters.my_json)

NOTE: For me it is pipeline parameter, in your case it will be the lookup output that produced the above given sample JSON.

enter image description here

When I debug the pipeline, it runs successfully and inserts the values into the respective table.

enter image description here