How to use JSON on Blob Storage as parameter for Azure Data Factory?

user_10660847 0
Viewed 947

I have a stored procedure with a parameter DateTime on ADF, and now I need to convert my parameter as a .JSON file that stored at Blob Storage.

My question is, is it possible to use JSON file on Azure Blob Storage ? Because on Azure Data Factory I seen there were JSON option on dynamic content.

If it is possible, is there any different parameter usage on my Stored Procedure SQL Script ? and how to call my JSON file to my pipeline? Do I need to specified my JSON folder on blog storage?

This is what I think, from this

enter image description here

To this

enter image description here

azure-data-factory azure-blob-storage azure sql-server json
1 Answers

We can use Lookup activity to find the value in a json file, then output the value to a variable in ADF.

  1. I sotred my json file in my Azure datalake.
{
    "intial_date":"2020-01-07",
    "end_date":"2020-01-07"
}

This is my stored procedure:

CREATE PROCEDURE dbo.test 
@end_date SMALLDATETIME, @initial_date SMALLDATETIME, @datediff INT 
as
begin

    print(@end_date)
    print(@initial_date)
end

  1. Then I use a Lookup activity to fetch the value. enter image description here

  2. In my stored procedure:

end_date: @activity('Lookup1').output.firstRow.end_date
initial_date: @activity('Lookup1').output.firstRow.initial_date

enter image description here

user_139794870
Related