0

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.

Baobab
  • 5
  • 3
  • Have to tried anything other than using fail activity? If so, include that additional information as well. – Saideep Arikontham Nov 10 '22 at 01:00
  • you are going to have to include more information in your question, but it sounds like you will need to use an `if condition activity` https://learn.microsoft.com/en-us/azure/data-factory/control-flow-if-condition-activity to control whether pipelines B & C are executed. – SOfanatic Nov 10 '22 at 01:21
  • @SaideepArikontham I can use a dummy activity to end the pipeline when no row is returned but this may call the other pipelines in sequence. A situation that I would like to prevent. There is need to send a message to the main pipeline not to execute the other pipelines as it's not necessary – Baobab Nov 10 '22 at 01:38
  • @SOfanatic I agree we need an If activity that determines if rows were produced from the query or not. When we don't have rows I would like to terminate the pipeline without a failure message. When this situation occurs the main pipeline shouldn't other pipelines in sequence – Baobab Nov 10 '22 at 01:41
  • Does pipeline A only have lookup activity or are there many activities and you want to use output of one look up (count of rows returned) and check whether to proceed or not? And what is the type of source table for this look up? – Saideep Arikontham Nov 10 '22 at 06:18

1 Answers1

0

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

enter image description here

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

enter image description here

  • 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).

enter image description here

Saideep Arikontham
  • 5,558
  • 2
  • 3
  • 11
  • I like the approach. Another way can be using a global parameter say count. It would be populated post copy activity from source. On the main pipeline the same approach will used as you describe but instead of querying a table check is done on the count parameter. If it has no value nothing is done. Otherwise the pipeline continues with the other steps – Baobab Nov 15 '22 at 06:06