how to pass value as not null in the ADF pipeline variable in if condition

Question

how to pass value as not null in the ADF pipeline variable in if condition

Anonymous

how to pass value as "is not null" in the ADF copy activity and how to build the expression in if condition?

ex: if @value is not null

Begin

statements

end

Anonymous

2023-05-16T14:51:44.64+00:00

I need to build the logic in ADF using IF condition.

ex: Declare @value int

if @value is not null

insert into table

select * from table1

where table1.value=@value
QuantumCache 20,356 Reputation points Moderator

2023-05-16T16:57:36.2266667+00:00

Hello @Bommisetty, Rakesh (Hudson IT Consultant),

Did you try to use any expression in the If Condition ?

VasimTamboli 5,135

In Azure Data Factory (ADF), you can use the "isnull" function to check if a variable has a null value. To pass a value as "is not null" in an ADF pipeline variable in an if condition, you can use the "not(isnull())" expression. Here's an example of how to build the expression in an if condition:

kotlin
Copy code
@if(not(isnull(variables('value'))))
begin
    // Statements to execute if 'value' is not null
end
In the above expression, the variables('value') represents the variable you want to check for null. The isnull() function returns true if the variable is null, and the not() function negates the result, making it true if the variable is not null.

You can replace the // Statements to execute if 'value' is not null comment with the actual statements you want to execute when the condition is met.

Remember to adjust the variable name to match the one you are using in your pipeline.

Sedat SALMAN 14,165 Reputation points MVP

2023-05-17T00:56:01.45+00:00
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#empty

you can use the empty function to check if a variable or value is null or empty

"@not(empty(pipeline().parameters.yourParameter))"

In Azure Data Factory (ADF), you can use an If Condition activity to control the flow of your pipeline based on a condition. If you want to check if a variable is not null and then perform some actions (such as running a copy activity), here's how you can do it:

Create a pipeline variable: In your ADF pipeline, declare a variable, say value. This variable can be populated by a previous step in your pipeline or passed in as a pipeline parameter.

Create an If Condition activity: Add an If Condition activity to your pipeline. In the expression for the If Condition activity, you can use the not(empty(...)) function to check if value is not null. The expression would look like this: @not(empty(pipeline().variables.value)). This expression will return true if value is not null, and false otherwise.

Define actions for True condition: If the condition returns true (i.e., value is not null), you can define the actions to be performed. In your case, it will be running a Copy activity. In the Copy activity, you can define a source query like SELECT * FROM table1 WHERE table1.value = @pipeline().variables.value, and target as your desired destination table.

If you want to handle the false case where value is null, you can define those actions under the "False activities" section of the If Condition activity.
Anonymous

2023-05-17T06:23:36.0233333+00:00

I tried to use not function.

@not(variable('valueID')) but this variable is not allowing me to pass it in the expression
Anonymous

2023-05-17T06:26:58.2933333+00:00

isnull function is not available in if condition activity when i written in the expression its throwing an error like "isnull is unknow function name"
Anonymous

2023-05-17T06:27:22.55+00:00

I am trying this today and will update you
Anonymous

2023-05-17T07:30:35.9166667+00:00

I tried with your approach but in copy activity getting below error. the select clause is not taking the variable.

i written the select query like this:

SELECT ID,textvalue from table

where ID=@variable('ID')

the above query throwing below error.

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Must declare the scalar variable "@variables".',Source=,''Type=System.Data.SqlClient.SqlException,Message=Must declare the scalar variable "@variables".,Source=.Net SqlClient Data Provider,SqlErrorNumber=137,Class=15,ErrorCode=-2146232060,State=2,Errors=[{Class=15,Number=137,State=2,Message=Must declare the scalar variable "@variables".,},],'

2 answers

Your answer

Anonymous

2023-05-16T14:51:44.64+00:00

I need to build the logic in ADF using IF condition.

ex: Declare @value int

if @value is not null

insert into table

select * from table1

where table1.value=@value
QuantumCache 20,356 Reputation points Moderator

2023-05-16T16:57:36.2266667+00:00

Hello @Bommisetty, Rakesh (Hudson IT Consultant),

Did you try to use any expression in the If Condition ?
VasimTamboli 5,135 Reputation points

2023-05-16T17:18:22.05+00:00

In Azure Data Factory (ADF), you can use the "isnull" function to check if a variable has a null value. To pass a value as "is not null" in an ADF pipeline variable in an if condition, you can use the "not(isnull())" expression. Here's an example of how to build the expression in an if condition: kotlin Copy code @if(not(isnull(variables('value')))) begin // Statements to execute if 'value' is not null end In the above expression, the variables('value') represents the variable you want to check for null. The isnull() function returns true if the variable is null, and the not() function negates the result, making it true if the variable is not null. You can replace the // Statements to execute if 'value' is not null comment with the actual statements you want to execute when the condition is met. Remember to adjust the variable name to match the one you are using in your pipeline.
Sedat SALMAN 14,165 Reputation points MVP

2023-05-17T00:56:01.45+00:00

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#empty

you can use the empty function to check if a variable or value is null or empty

"@not(empty(pipeline().parameters.yourParameter))"

In Azure Data Factory (ADF), you can use an If Condition activity to control the flow of your pipeline based on a condition. If you want to check if a variable is not null and then perform some actions (such as running a copy activity), here's how you can do it:

Create a pipeline variable: In your ADF pipeline, declare a variable, say value. This variable can be populated by a previous step in your pipeline or passed in as a pipeline parameter.

Create an If Condition activity: Add an If Condition activity to your pipeline. In the expression for the If Condition activity, you can use the not(empty(...)) function to check if value is not null. The expression would look like this: @not(empty(pipeline().variables.value)). This expression will return true if value is not null, and false otherwise.

Define actions for True condition: If the condition returns true (i.e., value is not null), you can define the actions to be performed. In your case, it will be running a Copy activity. In the Copy activity, you can define a source query like SELECT * FROM table1 WHERE table1.value = @pipeline().variables.value, and target as your desired destination table.

If you want to handle the false case where value is null, you can define those actions under the "False activities" section of the If Condition activity.
Anonymous

2023-05-17T06:23:36.0233333+00:00

I tried to use not function.

@not(variable('valueID')) but this variable is not allowing me to pass it in the expression
Anonymous

2023-05-17T06:26:58.2933333+00:00

isnull function is not available in if condition activity when i written in the expression its throwing an error like "isnull is unknow function name"
Anonymous

2023-05-17T06:27:22.55+00:00

I am trying this today and will update you
Anonymous

2023-05-17T07:30:35.9166667+00:00

I tried with your approach but in copy activity getting below error. the select clause is not taking the variable.

i written the select query like this:

SELECT ID,textvalue from table

where ID=@variable('ID')

the above query throwing below error.

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Must declare the scalar variable "@variables".',Source=,''Type=System.Data.SqlClient.SqlException,Message=Must declare the scalar variable "@variables".,Source=.Net SqlClient Data Provider,SqlErrorNumber=137,Class=15,ErrorCode=-2146232060,State=2,Errors=[{Class=15,Number=137,State=2,Message=Must declare the scalar variable "@variables".,},],'

Answer 1

QuantumCache 20,356 Moderator

Hello @Bommisetty, Rakesh (Hudson IT Consultant)

Please have a look at the below pipeline, hope it helps with you initial Query!

I have created a Pipeline Variable called 'varValue'

I am verifying the Null Value in the 'If Condition' and then i have added Activity based on the expression result.

@not(empty(variables('varValue')))

User's image

Inside the 'If Condition'-->Copy Data-->Query as shown below.

select * from dbo.employeeSource where City!='@{variables('varValue')}'

IF the Condition is True then the True Activity will be executed and and the Query is again using the Pipeline Variable as shown below.

User's image

QuantumCache 20,356 Reputation points Moderator

2023-06-05T16:10:53.14+00:00

Hello @Bommisetty, Rakesh (Hudson IT Consultant) How are you doing!
Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!

If the response is helpful, please click "Accept Answer" and upvote it. So that we can close this thread.

Answer 2

Bongo 21

Good Day

I am also not finding joy with. ["@not(empty())"].

Is there something I am missing.

Share via

how to pass value as not null in the ADF pipeline variable in if condition

2 answers

Your answer