Executing Stored Procedures in Fabric Data Factory Pipelines
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?
Community Votes
67% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
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.
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.
Community Discussion (4 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
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.
Related Analysis
Practice All DP-600 Questions
Access 115 questions with complete answers and detailed explanations.
View Full DP-600 Practice Test →