Evaluating a bit/boolean in ADF If Condition Activity

Question

Evaluating a bit/boolean in ADF If Condition Activity

Pendleton, David 25

You would think this would be incredibly simple, but apparently not.

A SQL statement used in a lookup returns either CAST(1 as bit) or CAST(0 as bit) to a pipeline as column is_enabled. An If condition activity (inside a For Each) uses this expression to determine which action to take:

@equals(item().is_enabled, true)

Unfortunately, this returns true regardless of the value of is_enabled. What is the correct way to accomplish this most basic of operations?

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-11-13T21:56:24.4133333+00:00
@Pendleton, David - Thanks for the question and using MS Q&A platform.

When dealing with boolean values in Azure Data Factory (ADF) and SQL, you need to be mindful of how the data types are interpreted by ADF, as it can sometimes lead to unexpected results. Since you're retrieving a bit value from SQL, it's important to ensure that it is properly interpreted as a boolean in your ADF pipeline.

Here's a step-by-step guide to correctly evaluate the bit/boolean value in an ADF If Condition activity:

SQL Query: Ensure that your SQL query returns the bit value correctly. If your SQL query is something like this:

SELECT CAST(is_enabled AS bit) AS is_enabled FROM your_table

This should return either 0 or 1.

Lookup Activity in ADF: Use a Lookup activity to fetch the data. The data returned by the Lookup activity will have the is_enabled field with values 0 or 1.

ForEach Activity: Ensure that your ForEach activity is correctly configured to iterate over the items returned by the Lookup activity.

If Condition Activity: Inside the ForEach activity, you need to properly evaluate the boolean value. Here's how you can do it:

Expression to Convert Bit to Boolean: You need to convert the bit value (0 or 1) to a boolean. In ADF, you can use the bool function to achieve this.
@equals(bool(item().is_enabled)
This expression will correctly convert the value of is_enabled to a boolean and then compare it to true.

Example Configuration: Here is an example of how your If Condition activity inside the ForEach activity should look:

If Condition Activity:

Name: CheckIfEnabled

Condition:

@equals(bool(item().is_enabled)

True Activity: Add the activities that should be executed if is_enabled is true.

False Activity: Add the activities that should be executed if is_enabled is false.

By following these steps, you ensure that the bit value returned from SQL is correctly interpreted as a boolean in ADF, and the If Condition activity will behave as expected.

Hope this helps. Do let us know if you have any further queries.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-11-14T23:46:35.27+00:00

@Pendleton, David - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-11-15T18:43:42.26+00:00

@Pendleton, David - Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Pendleton, David 25 Reputation points

2024-11-18T14:24:27.4933333+00:00

Thank you for the response. This actually turned out to be a GIT problem where changes were out of sync between branches.

After some doing, I was able to get everything synched and it works as it should. Thank you again.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-11-18T16:06:12.6866667+00:00

@Pendleton, David - 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.

1 answer

Your answer

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-11-14T23:46:35.27+00:00

@Pendleton, David - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-11-15T18:43:42.26+00:00

@Pendleton, David - Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Pendleton, David 25 Reputation points

2024-11-18T14:24:27.4933333+00:00

Thank you for the response. This actually turned out to be a GIT problem where changes were out of sync between branches.

After some doing, I was able to get everything synched and it works as it should. Thank you again.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2024-11-18T16:06:12.6866667+00:00

@Pendleton, David - 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.

Answer 1

@Pendleton, David - 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.

Ask: Evaluating a bit/boolean in ADF If Condition Activity

Solution: This actually turned out to be a GIT problem where changes were out of sync between branches. After some doing, I was able to get everything synched and it works as it should.

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.

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.

Share via

Evaluating a bit/boolean in ADF If Condition Activity

1 answer

Your answer