in adf it is not able to compare two char variables.

Question

{
"name": "untildemo",
"properties": {
"activities": [
{
"name": "Until1",
"type": "Until",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@bool(variables('untilflag'))",
"type": "Expression"
},
"activities": [
{
"name": "Wait1",
"type": "Wait",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"waitTimeInSeconds": 2
}
},
{
"name": "Lookup1",
"type": "Lookup",
"dependsOn": [
{
"activity": "Wait1",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "SqlServerSource",
"sqlReaderQuery": "select refer as status from demo",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"dataset": {
"referenceName": "SqlServerTable1",
"type": "DatasetReference"
}
}
},
{
"name": "If Condition1",
"type": "IfCondition",
"dependsOn": [
{
"activity": "Lookup1",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@equals(activity('Lookup1').output.firstRow.status,'Y')",
"type": "Expression"
},
"ifTrueActivities": [
{
"name": "Set variable1",
"type": "SetVariable",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"variableName": "untilflag",
"value": "true"
}
}
]
}
}
],
"timeout": "0.12:00:00"
}
},
{
"name": "Copy data1",
"type": "Copy",
"dependsOn": [
{
"activity": "Until1",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "SqlServerSource",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"sink": {
"type": "DelimitedTextSink",
"storeSettings": {
"type": "AzureBlobStorageWriteSettings"
},
"formatSettings": {
"type": "DelimitedTextWriteSettings",
"quoteAllText": true,
"fileExtension": ".txt"
}
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false
}
}
},
"inputs": [
{
"referenceName": "SqlServerTable1",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "DelimitedText1",
"type": "DatasetReference"
}
]
}
],
"variables": {
"untilflag": {
"type": "String",
"defaultValue": "false"
}
},
"annotations": []
}
}

Answer 1

Hi @sankar challa ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

From the above query, what I understand is that you are trying to compare the data present in refer column of demo table. Once the value equals Y , then set the variable untilflag to True. Kindly let me know if my understanding is incorrect.

I tried to repro your requirement and didn't find any issue.

Case 1: When the 'status' value is Y , it means that the if condition @equals(activity('Lookup1').output.firstRow.status,'Y') would return true which would eventually change the variable value to True. Once variable becomes True , it means untill block would result in True @bool(variables('untilflag')) as it's checking that until the variable returns true keep on executing the activities inside. The execution will end after the first loop.

Case 2: When the 'status' value is N , it will keep on running the activities inside until block until the status becomes Y . Once @activity('Lookup1').output.firstRow.status returns Y , the loop will break.

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

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

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

Answer 2

Hello @sankar challa ,

Thanks for sharing additional screenshot of your Lookup activity response JSON payload. One thing I have noticed from Lookup response JSON payload, there is a space in the value for the column status (status:"Y ")

Below observation is from the screenshot you have shared:

Below screenshot from @AnnuKumari-MSFT GIF (where the lookup field doesn't contain any space after letter Y or N which is why Anu condition is evaluating as expected).

Since there is a space which is causing this problem, there are two workarounds to overcome it.

Workaround 1: Either make sure source data in your table/column doesn't have leading or trailing spaces after "Y" / "N".
Workaround 2: Or you can even Trim the leading/trailing spacing using Trim() function to just consider the "Y" or "N" and truncate the extra spaces. Sample dynamic expression would look like @equals(trim(activity('Lookup1').output.firstRow.status), 'Y') in your If condition activity

Hope this info helps to resolve your issue. Do let us know how it goes.

Thank you

----------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Answer 3

I tried in other way by changing the column refer(char data type) to column ID(int data type) from table demo . Hence the column changed i will get the integer value of 1 in the place of 'Y', i compared it ....it looks something like this @equals(activity('Lookup1').output.firstRow.status,'1') with this i get the desired result.

{
"name": "untildemo",
"properties": {
"activities": [
{
"name": "Until1",
"type": "Until",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@bool(variables('untilflag'))",
"type": "Expression"
},
"activities": [
{
"name": "Wait1",
"type": "Wait",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"waitTimeInSeconds": 2
}
},
{
"name": "Lookup1",
"type": "Lookup",
"dependsOn": [
{
"activity": "Wait1",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "SqlServerSource",
"sqlReaderQuery": "select id as status from demo",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"dataset": {
"referenceName": "SqlServerTable1",
"type": "DatasetReference"
}
}
},
{
"name": "If Condition1",
"type": "IfCondition",
"dependsOn": [
{
"activity": "Lookup1",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@equals(activity('Lookup1').output.firstRow.status,1)",
"type": "Expression"
},
"ifTrueActivities": [
{
"name": "Set variable1",
"type": "SetVariable",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"variableName": "untilflag",
"value": "true"
}
}
]
}
}
],
"timeout": "0.12:00:00"
}
},
{
"name": "Copy data1",
"type": "Copy",
"dependsOn": [
{
"activity": "Until1",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "SqlServerSource",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"sink": {
"type": "DelimitedTextSink",
"storeSettings": {
"type": "AzureBlobStorageWriteSettings"
},
"formatSettings": {
"type": "DelimitedTextWriteSettings",
"quoteAllText": true,
"fileExtension": ".txt"
}
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false
}
}
},
"inputs": [
{
"referenceName": "SqlServerTable1",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "DelimitedText1",
"type": "DatasetReference"
}
]
}
],
"variables": {
"untilflag": {
"type": "String",
"defaultValue": "false"
}
},
"annotations": [],
"lastPublishTime": "2022-08-25T10:20:33Z"
},
"type": "Microsoft.DataFactory/factories/pipelines"
}