Check If the SQL Table contains a variable in Azure Data factory

Question

I need to check if particular SQL database table contains variable currentID.

Currently, I am running one lookup activity (name - GlobalIDs) to query the SQL table and retrieve the data. Output of the lookup activity GlobalIDs is as shown below

{
"count": 4,
"value": [
    {
        "Id": 1200934
    },
    {
        "Id": 1345323
    },
    {
        "Id": 1200932
    },
    {
        "Id": 1200334
    }
]
}

I also have another lookup activity (name - CurrentIDs) which returns another list of current ID values. I have a For each block within which I am looping through each of the CurrentIDs and setting a variable currentID to the iterated value.

Considering one of the iterations of ForEach now, Output of setVariable currentID is as follows:

{ 
  "name": "currentId",
  "value": "1345323"
}

Now, what I want to do is add an If condition to check if this currentId value exists in GlobalIDs lookup output values.

I tried using an if condition with contains as below - @contains(activity('GlobalIDs').output.value, variables('currentId'))

Tried few suggestions found online, but If condition does not seem to be working this way, as it always goes to false, and I do not see any input or output being rendered for the condition.

Answer 1

Hi @SR

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Issue: I need to check if particular SQL database table contains variable currentID.

Currently, I am running one lookup activity (name - GlobalIDs) to query the SQL table and retrieve the data. Output of the lookup activity GlobalIDs is as shown below.

{

I also have another lookup activity (name - CurrentIDs) which returns another list of current ID values. I have a For each block within which I am looping through each of the CurrentIDs and setting a variable currentID to the iterated value.

Considering one of the iterations of ForEach now, Output of setVariable currentID is as follows:

YAMLAI ConvertCopy

{

Now, what I want to do is add an If condition to check if this currentId value exists in GlobalIDs lookup output values.

I tried using an if condition with contains as below - @contains(activity('GlobalIDs').output.value, variables('currentId'))

Tried few suggestions found online, but If condition does not seem to be working this way, as it always goes to false, and I do not see any input or output being rendered for the condition.

Solution: The below usage worked for me. Had missed taking into consideration that JSON object was being compared with a string.

JSONAI ConvertCopy

@contains(activity('GlobalIDs').output.value, json(concat('{"Id": ',variables('currentId'), '}')))

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.