How terminate pipelines in Azure Synapse when query returns no rows

Question

I have a pipeline A that is invoke by a main pipeline D. It invokes 2 other pipelines B and C. When pipeline A is invoked an extraction query is executed that can return rows or nothing.

In case it returns no rows I would like it to terminate without sending an error message. It should also terminate the main pipeline D. In other words pipelines B and C shouldn’t be invoked. How can I invoke such a terminal activity in Azure Synapse? I would like to avoid using a Fail activity as it would be a false negative.

Answer 1

Since your child pipeline has the look up output count, and there is direct way to pass the count to master pipeline, you can consider changing the pipeline configuration.

• Instead of using lookup to get the count, you can directly use a copy data activity and write the count of records to a new table.

• You can get this data (new table data) using look up in master pipeline and perform the check (whether count is 0 or not).

• Look at the following demonstration. I have a table with no records in my azure SQL database. In Pipeline A, I have used the following query as source of copy data activity and auto created a table in sink.

-- in source. Querying the required table for count
select count(*) as count from demo

• Now in master pipeline, use an additional lookup to read records from above created count_val table. The output will be as follows:

• Now you can use this count in if condition using the following dynamic content:

@equals(activity('Lookup1').output.value[0].count,0)

• The condition will be true when the count is 0 (as show below) and hence the flow stops (no activities inside true case). If there are records, then the next pipelines will be executed (false case).