Issue with ForEach Activity in an IF Activity

[agustingp]

I'm encountering an issue where I'm unable to place a ForEach activity within an IF activity in my pipeline. As a workaround, I created a "Dummy IF" that splits the flow based on whether it fails or succeeds. However, this approach incorrectly marks the entire flow as "Run Failed," which is misleading because the flow actually runs as intended.

Is there a better way to structure this pipeline to avoid the "Run Failed" status and improve the overall process?

For context, I'm working on a pipeline that updates data in an external MySQL database to match a view in my data warehouse. The goal is to perform updates every 3 hours. Ideally by running INSERT, UPDATE, and DELETE statements that are generated by the pipeline, as this method is more efficient and minimizes the impact on production.

The pipeline has the following steps (shown in the image below):

1. Get Rows to Alter: This SQL query compares data between the source and my data warehouse table to determine which rows need to be updated, inserted, or deleted.

2. Dummy IF: This step checks if the number of rows returned by the query in step 1 is greater than 100. If so, the step succeeds; otherwise, it fails. The condition for failure is set by dividing by zero (i.e., div(1/0)) when the number of rows is less than 100.

   • If the condition succeeds:
     • Erase the table in the destination.
     • Copy the entire table to the destination.
   • If the condition fails:
     • The flow enters a ForEach loop that generates different SQL statements (UPDATE, INSERT, DELETE) depending on what action is required.
     • Send the generated SQL statements to the destination using a lookup activity.

Thanks in advance for your help and time.

[Anonymous · Accepted solution]

Hi @agustingp ,

For the elements of the operation you mentioned in step 1, I think you can compare the data between the source and data warehouse tables to determine which rows need to be updated, inserted, or deleted.

Use the Set Variable activity to store the number of rows returned from the previous step.

Add the If Condition activity to check if the variable (number of rows) is greater than 100.

The procedure is shown below:

Get the rows to change → 2. Set the variable to → 3. If Condition:
Facts:
Erase the table
Copy the data

Fallacy:
ForEach loop
Find Activity

This approach should help avoid misleading "run failed" statuses and make the pipeline more efficient and easier to manage.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mikeburek · Accepted solution]

Pipelines make you think a little differently. For this, I would setup a second pipeline that has the For Loop. And change your "dummy IF" into a normal IF. Inside of the False section, make it run the second pipeline with the For Loop.

You should be able to pass values using the pipeline Parameters.

It's likely those 3 activities after your For Loop would go in to the pipeline for the For Loop.

[Anonymous · Accepted solution]

Hi @agustingp ,

For the elements of the operation you mentioned in step 1, I think you can compare the data between the source and data warehouse tables to determine which rows need to be updated, inserted, or deleted.

Use the Set Variable activity to store the number of rows returned from the previous step.

Add the If Condition activity to check if the variable (number of rows) is greater than 100.

The procedure is shown below:

Get the rows to change → 2. Set the variable to → 3. If Condition:
Facts:
Erase the table
Copy the data

Fallacy:
ForEach loop
Find Activity

This approach should help avoid misleading "run failed" statuses and make the pipeline more efficient and easier to manage.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.