question

abdulkalam-1616 avatar image
0 Votes"
abdulkalam-1616 asked AnnuKumari-MSFT commented

Azure Data Factory condition in Foreach activity

I have a requirement to load API data into cosmos db . So need to loop all the api pages for given period and load in cosmos db.

my pipeline has a web activity to get the api response first . then passing the page_count to foreach activity to loop through.

When there is data in API response the output looks below .

202863-image.png

When API doesnt have data the output looks below.

202819-image.png

I have written below condition in foreach activity for looping . Since the web activity output not same in both conditions , the below code not working .any suggestion please.

@range(1,if(contains(activity('WEB_GET_NUMBEROFPAGES').output.Response,'No data found'),1,activity('WEB_GET_NUMBEROFPAGES').output.ameta[0].page_count))










azure-data-factory
image.png (12.0 KiB)
image.png (13.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnnuKumari-MSFT avatar image
0 Votes"
AnnuKumari-MSFT answered AnnuKumari-MSFT commented

Hi @abdulkalam-1616 ,
Thankyou for the detailed description about your ask. In the above expression , one or the other property would always be missing , either 'Response' or 'ameta[]' . So, we need to first check the available properties , before using them in the expression.

Here is what you need to do:
1. Create a variable var1 of type 'String'. Use Set variable activity after the Web activity to store the output of Web activity. Use this in the value of set variable activity: @{activity('Web1').output} or @string(activity('Web1').output)

203262-image.png
2. Use If Condition activity with expression : @contains(variables('var1'),'No data') .
a. Inside True condition block: Use Execute Pipeline activity and create a new pipeline . In the new pipeline, Use For Each activity and have this
expression as the Items value: @range(1,1)

203186-image.png
b. Inside False condition block: Use Execute Pipeline activity and create a new pipeline. In the new pipeline , Create a parameter named page_count and go back to the False condition block and provide activity('WEB_GET_NUMBEROFPAGES').output.ameta[0].page_count as the value for page_count variable. Use For Each activity in the new pipeline called inside False block and provide this as items value: @range(1,int(pipeline().parameters.page_count))

203263-image.png

Note: In the images, I have used lookup instead of web activity to reproduce your scenario.

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 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



image.png (39.4 KiB)
image.png (51.7 KiB)
image.png (54.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Many thanks for the detailed steps and screenshot. Much appreciated... Its working as expected :).

One question on copy activity - Can we change the source datatype and inserting into sink ? My scenarios is REST api returns all fields in string want to convert few in integer and load into cosmos DB.

1 Vote 1 ·

@abdulkalam-1616 ,
Glad to know it helped. Thanks for your confirmation. For the followup query, as it is not related to this thread, I would recommend you to create a new thread with the same ask. Please do consider clicking Accept Answer and take satisfaction survey for this thread as accepted answers help community as well. Thanks !

0 Votes 0 ·
AnnuKumari-MSFT avatar image
0 Votes"
AnnuKumari-MSFT answered AnnuKumari-MSFT commented

Hi @abdulkalam-1616 ,

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

As I understand your ask , it seems you are trying to loop through each of the responses of Web Activity inside ForEach activity based on the condition:
If Webactivity output contains 'No data found', items value should be @range(1,1) else @range(1,page_count) . However, it's not working as expected. Please correct me if my understanding is incorrect.

Please try the following expression: @range(1,if(contains(activity('WEB_GET_NUMBEROFPAGES').output.Response.data[0],'No data found'),1,activity('WEB_GET_NUMBEROFPAGES').output.ameta[0].page_count)) as 'No data found' is part of data[] array , we need to mention that to check if that string is part of the output or not.

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 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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for looking into this AnnuKumari .

When there is a data in the api output, the activity('WEB_GET_NUMBEROFPAGES').output.Response.data[0] becomes invalid and
when there is no data in the output the activity('WEB_GET_NUMBEROFPAGES').output.ameta[0].page_count becomes invalid.

The error from pipeline is below.
The expression 'length(range(1,if(contains(activity('WEB_GET_NUMBEROFPAGES').output.Response.data[0],'No data found'),1,activity('WEB_GET_NUMBEROFPAGES').output.ameta[0].page_count)))' cannot be evaluated because property 'Response' doesn't exist, available properties are 'ameta, data, ADFWebActivityResponseHeaders, effectiveIntegrationRuntime, executionDuration, durationInQueue, billingReference'.

It seems when data not available it is returning invalid json , i am checking this with source provider on this.
Thanks again for looking into this.

0 Votes 0 ·

Hi @abdulkalam-1616 ,
Kindly have a look at the below answer.

0 Votes 0 ·