ADF - Pass array to a SQL stored procedure

0
votes

I have an Azure data factory pipeline which calls a SQL stored procedure to perform some operation. I have an array within my pipeline and I want to pass this array to the stored procedure. I tried to create a custom type in SQL server and tried passing the array as input. But it didn't work as the activity does not have a data type for parameter as array. Any idea how to pass an array to a SQL stored procedure?

Screenshot of ADF option

enter image description here

1
sql-serverazureazure-sql-databaseazure-data-factory
Please use String as the data type. How did you get the array? Is the the output of some active? – Leon Yue
@LeonYue - I get all the file names from a fileshare location using "Get MetaData" task. I need to pass it to the Stored procedure. If string is the only way, is there any easy way to convert the array to string? – user12526031
you don't need to convert it. The output of the Get MetaData is already string data type. I have the question , are the parameters of the stored procedure the element of the array? – Leon Yue
I am trying to get all the files from a folder. So the output of get metadata has a field named childItems and it is an array – user12526031
Use a string and then convert your array into a JSON object, and then use OPENJSON to parse the JSON array into a table – mauridb

1 Answers

0
votes

The output 'childitems' of the get metadata is a json object, not an array. enter image description here

The pipeline should like this firstly: pass the childitems(file names) to the stored procedure: enter image description here

I don't know how you program your stored procedure, but just make some changes to accept the json parameter.

Ref: How to Insert JSON Data into SQL Server Database