Question 379178 in Azure Question posted in
The official documentation to get you started can be found here.

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

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": 30,
"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.

Tags:

2

Answers


  1. 0

    Looks like GlobalIDs is an array of objects. Each instance has a single property – Id.

    What we can do is contruct an object with this property corresponding to currentId, as follows:

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

    Update your condition accordingly.

    Login or Signup to reply.
  2. 0

    • I have taken the same setup as you did with two lookup activities and one for-each activity.

    • Make sure you have unchecked First row only option on both lookup activities.

    • In For-each activity, select Sequential check box. This will make sure the value is going sequentially.

    • Inside for-each activity, added set variable activity and if activity.

    -The condition for if activity is given as,

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

    This ensures if a SQL table contains value, it will result in true.

    [If for-each activity is parallel, then all activities inside for-each will be executed parallel. Thus, the input for if condition activity will not be same as expected. It would be anything. That could also be the reason for the resulting in false for all cases.]

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search