Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
7,138 questions
how to pass value as not null in the ADF pipeline variable in if condition
Bommisetty, Rakesh (Hudson IT Consultant)
40
Reputation points
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
{count} votes
-
Bommisetty, Rakesh (Hudson IT Consultant) 40 Reputation points
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
-
SatishBoddu-MSFT 15,121 Reputation points2023-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 2,245 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 4,775 Reputation points2023-05-17T00:56:01.45+00:00 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.
-
Bommisetty, Rakesh (Hudson IT Consultant) 40 Reputation points
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
-
Bommisetty, Rakesh (Hudson IT Consultant) 40 Reputation points
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"
-
Bommisetty, Rakesh (Hudson IT Consultant) 40 Reputation points
2023-05-17T06:27:22.55+00:00 I am trying this today and will update you
-
Bommisetty, Rakesh (Hudson IT Consultant) 40 Reputation points
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".,},],'
-
SatishBoddu-MSFT 15,121 Reputation points
2023-05-18T04:31:20.34+00:00 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')))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.
Sign in to comment