problem with If Condition proper setup

Question

I'm building a flow in my Azure Data factory pipeline where want to setup some conditional logic.
The purpose is to validate the blob files were copied properly into Azure SQL Database and if so move the blob files to a backup folder or if not move them to another folder to review.

I am doing several things in the pipeline but a focus here is on two of them:

• I set up a variable to check if the file was loaded to sql db. It simply check if the row with the file name exists in a table.
  If yes, returns 1, if not returns 0.
  
• in the second step i built "If Condition" logic based on the variable defined in the previous step.
  In case the the variable value is 1 I want to copy the data to a backup folder.
  In case the the variable value is 0 I want to copy the data to a another folder.

I am not sure this logic is correct. I get the below error when debuging the pipeline on the if condition step.

Thanks for any sugestions

Answer 1

Thanks @BartoszWachocki-4076 for posting the additional details. Please find below observations

• "Set Variable" activity cannot connect to SQL database and fetch the result. It is treating the query as string and hence it is trying to match it with 1. Since it is never same, it is always going to false
• "Lookup" activity can be used when we need results from SQL - I have created a stored procedure at SQL and called it from lookup to capture the result of case statement shown above
• I checked with product team and converting the string variable to int is making bool function work without having to use equals as well. @bool(int(string(activity('Lookup1').output.firstRow.value)))

All this is shown in GIF below.

Hope this helps! Please let us know for further queries and we will be glad to assist.

Answer 2

Thanks @HarithaMaddi-MSFT

Just tested your workaround and can say that the pipeline run and there was no error as before. So that's fine.
Although my happines didn't last so long.

But what made me worried is that the whole logic didn't work as expected and honestly I do not understand why.
The "If condition" always returned FALSE. I took a closer look at the " set variable" activity and tested it in SSMS where it was working fine.
Here are the screenshots.

As you can observed on the first screen the value returned by SQL query used in variable returned 0.
Whilst on the second return 1. So this is fine. In SSMS it is working correctly.

But for some reason in ADF for above two cases the value returned by "If condition" was I beleve FALSE because
both files were copied to the same folder instead two separate.

Are you able to test on you side?
I have a doubt now if the values returned by variable are 1 or 0. Instead it may be a string or sth else but I do not know how to check it.
That make sens and would be possibly the reason of error with Bool() expression.