Set variable from lookup activity result

Question

Set variable from lookup activity result

Obaid UrRehman 171

Hi,

I have a lookup activity in and ADF pipeline connected to a set variable activity:

The query in lookup activity is:

Select name from [dbo].[someTable] where [ID] = '@{pipeline().parameters.pl_ID}'

name is actually an integer

The idea is to query the database, set a variable already_exisiting_name equals to the result from the query

I have a variable current_name, if the alread_exsiting_name is equal to current_name then pipelines fails otherwise if already_exisitng_name is empty (as in this case, when there is no data yet in the db) the pipeline continues or if already_exisitng_name is smaller than current_name the pipeline continues.

When Iexecute the lookup activity, and examine the output, I dnt see any name field:

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2022-03-23T11:19:14.46+00:00

Hey,
Can you please execute the lookup activity once with hardcoded value rather than a parameter value and also execute the same query via ssms and check whether you are getting any results in lookup output or not. That would help us debug further.
Obaid UrRehman 171 Reputation points

2022-03-23T11:23:59.167+00:00
Hi,

Actually, there is no data in the table, it is like a status table which gets populated once the pipeline run complete, but there is no data initially. With unchecking the First Row Only I get the following results:

{ "count": 0, "value": [], "effectiveIntegrationRuntime": "someRT", "billingReference": { "activityType": "PipelineActivity", "billableDuration": [ { "meterType": "SelfhostedIR", "duration": 0.016666666666666666, "unit": "Hours" } ] }, "durationInQueue": { "integrationRuntimeQueue": 5 } }

In above output I get count:0 because the query produced no results as there is no data.

But if there is some data I would always get count:1 because there will be only one row for each ID and will get output inside value array above.

Answer accepted by question author

0 additional answers

Your answer

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2022-03-23T11:19:14.46+00:00

Hey,
Can you please execute the lookup activity once with hardcoded value rather than a parameter value and also execute the same query via ssms and check whether you are getting any results in lookup output or not. That would help us debug further.
Obaid UrRehman 171 Reputation points

2022-03-23T11:23:59.167+00:00

Hi,

Actually, there is no data in the table, it is like a status table which gets populated once the pipeline run complete, but there is no data initially. With unchecking the First Row Only I get the following results:

{ "count": 0, "value": [], "effectiveIntegrationRuntime": "someRT", "billingReference": { "activityType": "PipelineActivity", "billableDuration": [ { "meterType": "SelfhostedIR", "duration": 0.016666666666666666, "unit": "Hours" } ] }, "durationInQueue": { "integrationRuntimeQueue": 5 } }

In above output I get count:0 because the query produced no results as there is no data.

But if there is some data I would always get count:1 because there will be only one row for each ID and will get output inside value array above.

Answer 1

Nandan Hegde 36,716 MVP Volunteer Moderator

Hey,
In case of lookup activity in case if there is no data in your output below are the 2 ways output is visible:

Disabling first row only

2) Enabling 1st row only

Based on your scenario, there might be cases wherein there might be no data or some data, so I would suggest disable the 1st row only property and have a below logic:
IF count=0 meaning no data , so default assign your variable a value else pick up the 1st row value of the column

ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-04-04T16:45:01.94+00:00

Hi @Obaid UrRehman ,

Just checking in to see if the above answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator

2022-04-05T16:10:30.497+00:00

Hi @Obaid UrRehman , Just checking is above answer helped? If yes, Please consider hitting Accept Answer button. Accepted answers help community as well. Please let us know if any further queries.

Share via

Set variable from lookup activity result

0 additional answers

Your answer