Expression failing in ADF foreach activity

Question

Expression failing in ADF foreach activity

Tej 146

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.

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.

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

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-11-29T11:34:57.337+00:00

Hi @Tej ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote and take satisfaction survey for the same as accepted answers help community as well. If you have any further query do let us know.
Tej 146 Reputation points

2022-11-29T11:49:20.41+00:00

Hi @AnnuKumari-MSFT ,

Thanks for your response, The approach you provided would work for me but there is one challenge, the scripts which I want to execute is in some folder, so I want to execute the scripts which is in specific folder.

Like e.g.

I want to only execute only the scripts inside the SQLScriptsToMigrate folder not inside any other folders. Could you please assist on how to run the scripts related to specific folder. I tried to use the filter activity after the web activity but was not able to fetch the required scripts.

Thanks in advance!!

Just to let you know, I want to use this synapse pipeline for deploying serverless sql objects to other environment.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-11-29T17:16:35.647+00:00

Hi @Tej ,

Your approach of using filter is correct. Kindly provide the following expression in condition: @if(contains(item().properties,'folder'),contains(item().properties.folder.name,'SQLScriptsToMigrate'),False)

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

Hope it helps. Kindly consider accepting answer and taking survey. Thanks
Tej 146 Reputation points

2022-12-01T11:59:34.45+00:00

Hi @AnnuKumari-MSFT ,

Thanks for your response, Now I am able to filter the scripts based on the folder name. Could you let me know if there is any limit for fetching the sql scripts through web activity because I have 24 sql scripts in the required folder and am able to fetch only 7 scripts in the output. Is it because of any limit as other scripts are there in different folder?

The expression am using ''@if(contains(item().properties,'folder'),contains(item().properties.folder.name,'Data Ingestion'),False)''

If there is any limit, Could you please assist on how to filter all the sql scripts inside the folder?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-05T06:18:06.993+00:00

Hi @Tej ,
Apologies for delay in response. The screenshot looks like Web activity output is giving 50 jsons as output. Out of 50 jsons, only 7 items got filtered out , which means only in 7 jsons has property called folder which holds values as : 'Data Ingestion' . Please verify .

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

Hope it helps. If this resolves your query, kindly consider marking this answer as accepted. This will help others find useful answers faster.
Tej 146 Reputation points

2022-12-05T08:35:41.123+00:00

Hi @AnnuKumari-MSFT ,

The webactivity itself is not fetching the entire sql scripts as it getting only 7 scripts out of 24. This looks like there is a limit of output of web activity as it can get only 4MB of output size. There is nextLink in the output of web activity as there is some output left out in next pages looks like.

Could you assist in this situation how to get the overall output of web activity and filter the sql scripts?

Thanks in advance.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-07T12:48:38.093+00:00

Hi @Tej ,
Unfortunately, there is no direct way to overcome the limitation of payload size of 4MB in web activity. As a workaround, I can suggest you to export the SQL scripts and then upload to storage and then read the scripts from the notebook.

Also, recommending you to vote here for increasing the limit of payload size of web activity. Thanks

-----------

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well
Tej 146 Reputation points

2022-12-07T18:27:06.01+00:00

Thanks for your response.

Accepted answer

0 additional answers

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-11-29T11:34:57.337+00:00

Hi @Tej ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote and take satisfaction survey for the same as accepted answers help community as well. If you have any further query do let us know.
Tej 146 Reputation points

2022-11-29T11:49:20.41+00:00

Hi @AnnuKumari-MSFT ,

Thanks for your response, The approach you provided would work for me but there is one challenge, the scripts which I want to execute is in some folder, so I want to execute the scripts which is in specific folder.

Like e.g.

I want to only execute only the scripts inside the SQLScriptsToMigrate folder not inside any other folders. Could you please assist on how to run the scripts related to specific folder. I tried to use the filter activity after the web activity but was not able to fetch the required scripts.

Thanks in advance!!

Just to let you know, I want to use this synapse pipeline for deploying serverless sql objects to other environment.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-11-29T17:16:35.647+00:00

Hi @Tej ,

Your approach of using filter is correct. Kindly provide the following expression in condition: @if(contains(item().properties,'folder'),contains(item().properties.folder.name,'SQLScriptsToMigrate'),False)

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

Hope it helps. Kindly consider accepting answer and taking survey. Thanks
Tej 146 Reputation points

2022-12-01T11:59:34.45+00:00

Hi @AnnuKumari-MSFT ,

Thanks for your response, Now I am able to filter the scripts based on the folder name. Could you let me know if there is any limit for fetching the sql scripts through web activity because I have 24 sql scripts in the required folder and am able to fetch only 7 scripts in the output. Is it because of any limit as other scripts are there in different folder?

The expression am using ''@if(contains(item().properties,'folder'),contains(item().properties.folder.name,'Data Ingestion'),False)''

If there is any limit, Could you please assist on how to filter all the sql scripts inside the folder?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-05T06:18:06.993+00:00

Hi @Tej ,
Apologies for delay in response. The screenshot looks like Web activity output is giving 50 jsons as output. Out of 50 jsons, only 7 items got filtered out , which means only in 7 jsons has property called folder which holds values as : 'Data Ingestion' . Please verify .

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

Hope it helps. If this resolves your query, kindly consider marking this answer as accepted. This will help others find useful answers faster.
Tej 146 Reputation points

2022-12-05T08:35:41.123+00:00

Hi @AnnuKumari-MSFT ,

The webactivity itself is not fetching the entire sql scripts as it getting only 7 scripts out of 24. This looks like there is a limit of output of web activity as it can get only 4MB of output size. There is nextLink in the output of web activity as there is some output left out in next pages looks like.

Could you assist in this situation how to get the overall output of web activity and filter the sql scripts?

Thanks in advance.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-07T12:48:38.093+00:00

Hi @Tej ,
Unfortunately, there is no direct way to overcome the limitation of payload size of 4MB in web activity. As a workaround, I can suggest you to export the SQL scripts and then upload to storage and then read the scripts from the notebook.

Also, recommending you to vote here for increasing the limit of payload size of web activity. Thanks

-----------

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well
Tej 146 Reputation points

2022-12-07T18:27:06.01+00:00

Thanks for your response.

Answer 1

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:

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

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

Please don't forget to click on or upvote 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

Share via

Expression failing in ADF foreach activity

0 additional answers

Your answer