Stored Procedure to take json string as an input then insert these string into SQL table as output in Azure Data Factory

0
votes

Continuing from my previous post: Azure Data Factory Get Metadata to get blob filenames and transfer them to Azure SQL database table

I am trying to write a stored procedure to take json string as input and insert this string into SQL table as an output in Azure Data Factory. This is the Stored Procedure activity in my Azure Data Factory. I already wrote and save the begging of my stored procedure which is in Azure SQL server.

StoredProcedure1

Here is the beginning of my Stored procedure: I got some ideas from this post: Creating a stored procedure which ingests data after reading a JSON string

StoredProcedure2

Here is my sample output.json file that I got it from the previous activity, Get Metadata of my pipeline. I basically need to get the value of itemName in this json file as an input of the stored procedure.

StoredProcedure3

Could you please help me how to continue this stored procedure? Thank you very much in advance.

1
stored-proceduresazure-data-factory

1 Answers

1
votes

If I were you I could have read the output of the metadata as below and I passed those to the proc parameter .

@activity('Get Metadata1').output.durationInQueue.integrationRuntimeQueue
@activity('Get Metadata1').itemName
@activity('Get Metadata1').itemType
@activity('Get Metadata1').effectiveIntegrationRuntime

The proc should something like

CREATE PROC XXXXX 
@effectiveIntegrationRuntime int 
,@itemName varchar(100)
,@itemType varchar(100)
,Metadata1').effectiveIntegrationRuntime int 

AS 
INSERT INTO SOMETABLEXXX  VALUES (@effectiveIntegrationRuntime .......)