I am using a stored procedure in synapse to create a view. This stored procedure written inside a ForEach group and I Need to pass view name and column name dynamically as an Array parameter in ForEach group.
I tried like this but it is not working.
[{"ViewName":"Table1","ColumnList":"file_name,field2"}]
0
You can follow below procedure to achieve your requirement:
Create an array pipeline parameter viewPrams with value
[{"ViewName": "view1", "ColumnList": "Id,Name"},{"ViewName": "view2", "ColumnList": "DateOfBirth,Email"} ]
In foreach activity use created parameter as items @pipeline().parameters.viewParams as shown below:
Inside the ForEach activity, add an Execute Stored Procedure activity to call your stored procedure, create parameters as below:
- ViewName:
@item().ViewName - ColumnList:
@item().ColumnList
By following these steps, you should be able to dynamically pass the view name and column list as an array parameter to your ForEach activity in Synapse. And after successful execution of pipeline, you will be able to create views successfully. List the views using below query:
SELECT TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
ORDER BY TABLE_SCHEMA, TABLE_NAME;
Output:
2