You have a Fabric tenant. You are creating a Fabric Data Factory pipeline. You have a stored procedure that returns the number of active customers and their average sales for the current month. You need to add an activity that will execute the stored procedure in a warehouse. The returned values must be available to the downstream activities of the pipeline. Which type of activity should you add?

The Script activity in a Microsoft Fabric pipeline is designed to execute one or more SQL statements, including stored procedures, against a supported data store like a Fabric warehouse. A key feature of the Script activity is its ability to return a result set or output values from the executed script. This output can then be consumed by subsequent, downstream activities within the same pipeline, directly fulfilling the requirement to make the returned values available for further processing.

B. Copy data: The primary purpose of the Copy data activity is to move data between data stores. It is not designed for executing a procedure solely to capture its output for pipeline control flow.

C. Switch: The Switch activity is a control flow construct that directs the pipeline's execution path based on an expression's value. It does not execute external scripts or stored procedures.

D. Append variable: This activity is used to add a value to an existing array variable within the pipeline's context. It does not have the capability to execute a stored procedure.

---

1. Microsoft Learn. (2024). Script activity in Microsoft Fabric.

Reference: In the "Overview" section

it states

"The Script activity allows you to execute a block of commands against a supported data store... You can also pass parameters into your script and return results back to the pipeline." This confirms its ability to execute scripts (like a stored procedure call) and make the results available downstream.

2. Microsoft Learn. (2024). Control flow activities in a Data Factory pipeline in Microsoft Fabric.

Reference: This document provides an overview of control flow activities. It describes the Switch activity as a conditional that "provides the same functionality that a switch statement provides in programming languages

" and the Append Variable activity as one that "add[s] a value to an existing array variable." Neither description includes executing external code like a stored procedure.