Executing Stored Procedures in Fabric Data Factory Pipelines

DP-600 By islever Editorial Last updated

Short answer

B Add a Script activity to execute the stored procedure and make the returned values available to downstream activities.

Question

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?
  1. Append variable
  2. Script Correct Answer
  3. Stored procedure
  4. Get metadata

Key Takeaways

The question tests the distinction between executing code versus retrieving data; the trap is assuming the 'Stored Procedure' activity handles return values, whereas it only executes without capturing outputs in Fabric.

This page clarifies which activity is required to execute a stored procedure in Microsoft Fabric Data Factory and capture its output for downstream use. It establishes that the Script activity is the correct choice when the stored procedure returns result sets or has output parameters, unlike the dedicated Stored Procedure activity.

Common mistake: Candidates often select 'Stored procedure' (Option C) because it seems logically obvious, but this activity in Fabric does not support returning values or output parameters to the pipeline variables/activities.

Expert Analysis

Why the Answer Is Correct

The Script activity (Option B) is designed to execute SQL queries or scripts against a Fabric warehouse and capture the results. When a stored procedure needs to return specific values (like counts or averages) to be used by downstream activities, the Script activity allows you to call the procedure and map the returned dataset to pipeline variables or pass it directly. This aligns with Microsoft's documentation stating that for scenarios requiring output parameters or result sets, the Script activity (often paired with Lookup logic if available, but Script is the execution vehicle here) is the appropriate tool.

Why the Other Options Are Wrong

Stored procedure (Option C) is incorrect because, in the context of Fabric Data Factory, this activity executes the procedure but does not expose the returned values or output parameters to subsequent pipeline steps. Append variable (Option A) is used to add items to an existing array variable, not to execute database logic. Get metadata (Option D) retrieves structural information about datasets (like column names) but does not execute business logic or stored procedures.

Community Comment Notes

Community consensus highlights a limitation: as noted by user Azure_2023, "Stored Procedure activity does not return any values." User 4e5cf3d reinforced this by citing MS documentation: "When the stored procedure has Output parameters... use lookup activity and Script activity." This confirms that while 'Stored Procedure' sounds right, it lacks the functionality to capture the 'returned values' requested in the prompt.

Exam Strategy

Always distinguish between 'execution' and 'data retrieval'. If the requirement involves getting back data (variables, results) from a database operation, look for 'Script', 'Lookup', or 'Web Activity'. If it's just running a process without needing the output, 'Stored Procedure' might suffice, but never assume it returns data.

Frequently Asked Questions

Why can't I use the Stored Procedure activity?

In Fabric Data Factory, the Stored Procedure activity executes the code but does not support returning output parameters or result sets to the pipeline.

Is there a better alternative to Script activity?

If the Lookup activity were an option, it would be preferred for simple value retrieval. However, for general script execution with outputs, Script is the correct choice among the provided options.

More DP-600 FAQ →

Community Votes

B
67%
C
33%

67% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 4 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 4

  1. wudixh 👍 2 Answered C
    According to MS Copilot: To execute the stored procedure and make the returned values available to downstream activities in your Fabric Data Factory pipeline, you should add a Stored procedure activity (option C). This activity is specifically designed to execute stored procedures in a database and can pass the output to subsequent activities in the pipeline.
  2. Azure_2023 👍 2 Answered B
    Not sure in what scenario I would use Script activity over lookup, but considering we do not have Lookup as an answer here, we are left with Script as the answer. Stored Procedure activity does not return any values.
  3. 4e5cf3d 👍 1 Answered B
    "When the stored procedure has Output parameters, instead of using stored procedure activity, use lookup activity and Script activity. Stored procedure activity does not support calling SPs with Output parameter yet." Literally on MS documentation
  4. nvukas 👍 1 Answered B
    Script or lookup

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Fabric Deployment Pipeline Folder Structure Behavior

S-grade

Accessing OneLake Shortcuts in Fabric Notebooks

S-grade

Direct Lake Fallback to DirectQuery with Row-Level Security

S-grade

Displaying All Rows in Power BI Python Visuals

S-grade

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide