Expression failing in ADF foreach activity

Tej 146 Reputation points
2022-11-24T18:20:57.347+00:00

Hi team,

I am facing the issue in executing the for each activity. I want to iterate through queries coming from the web activity output and execute it through stored procedure. Below is how the output of web activity looks and stored procedure screenshots.

263966-image.png

264025-image.png

Please let me know how to configure so that the queries containing stored procedures will iterate through for each and execute it through stored procedure activity.

263986-image.png

@ShaikMaheer-MSFT , @Bhargava-MSFT , Could you please assist on this?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,135 questions
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 32,011 Reputation points Microsoft Employee
    2022-11-28T06:43:37.447+00:00

    Hi @Tej ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your question, you want to run stored procedures using stored procedure activities in ADF pipeline, the procedures are present in synapse SQL scripts which is being fetched by the web activity. Please let me know if my understanding is incorrect.

    There is no direct way to achieve this , the workaround I can suggest is to use LookUp activity pointing to SQL database and use the following query:

    declare @webactivityoutput varchar(max) set @webactivityoutput = '@{activity('Web1').output.value}' select substring(query,CHARINDEX('[',query)-1,(CHARINDEX(']',query,CHARINDEX('.',query))+1)-(CHARINDEX('[',query)-1)) as procname from OpenJSON(@webactivityoutput) with(query varchar(max) '$.properties.content.query')

    After lookup , use foreach activity to iterate through each of the stored proc names and inside foreach , use stored procedure activity where in stored procedure name provide @item().procname

    Please check the below video for reference:

    264654-sprun2.gif

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button and take satisfaction survey whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments

0 additional answers

Sort by: Most helpful