Azure Data Factory Activity for Stored Procedure Output

You are creating an Azure Data Factory pipeline. You need to add an activity to the pipeline. The activity must execute a Transact-SQL stored procedure that has the following characteristics: • Returns the number of sales invoices for a current date • Does NOT require input parameters Which type on activity should you use?

  1. Stored Procedure
  2. Get Metadata
  3. Append Variable
  4. Lookup Source Reference Answer

Community Votes

D
83%
A
17%

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

Community Insight

Tests the distinction between activities: use Stored Procedure for execution only, but use Lookup when you need to capture and pass back the result of a query or stored procedure.

The Lookup activity is the correct choice for executing a stored procedure that returns a result set or scalar value, whereas the Stored Procedure activity does not support returning output values.

Candidates often select 'Stored Procedure' because it seems logically appropriate for running SQL code, failing to realize that this specific activity cannot return data to the pipeline.

Community Discussion (5 comments)

renan_ineu 👍 1 Selected: D
"Lookup activity reads and returns the content of a configuration file or table. It also returns the result of executing a query or stored procedure" - https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity#:~:text=returns%20the%20result%20of%20executing%20a%20query%20or%20stored%20procedure "When the stored procedure has Output parameters, instead of using stored procedure activity, use lookup acitivty and Script activity. Stored procedure activity does not support calling SPs with Output parameter yet" - https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure#:~:text=stored%20procedure%20activity%2C-,use%20lookup%20acitivty,-and%20Script%20activity
fahfouhi94 👍 1 Selected: D
When the stored procedure has Output parameters, instead of using stored procedure activity, use lookup acitivty and Script activity. Stored procedure activity does not support calling SPs with Output parameter yet.
Alongi 👍 2 Selected: D
The Stored Procedure Activity does not allow to return an output, so Lookup is the correct one. Refer to: https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure
JamieMcD 👍 1 Selected: D
Also ChatGPT: For your specific requirement: Retrieve the Number of Sales Invoices for the Current Date: If you simply need to call a stored procedure that returns the count and you want to use this count directly in your pipeline (e.g., for further processing or branching logic), the Lookup activity is preferable. If the stored procedure's primary purpose is to perform operations (e.g., insert/update/delete) and the output is not directly needed within the pipeline, the Stored Procedure activity is more appropriate. Given that your stored procedure returns the number of sales invoices and does not require input parameters, and assuming you want to use this count directly in the pipeline, the Lookup activity would be a good fit.
tadenet 👍 1 Selected: A
Chatgpt: The Stored Procedure activity in Azure Data Factory is specifically designed to execute SQL stored procedures. It is the most suitable activity when you need to run a stored procedure and handle the results or output of that procedure. The other options do not fit the requirements: B. Get Metadata is used to retrieve metadata information from data stores, not to execute stored procedures. C. Append Variable is used to append a value to an existing variable, not for executing stored procedures. D. Lookup is used to retrieve a dataset from a data store but is not typically used to execute stored procedures that return a single value without parameters.

Comments & Corrections

No comments 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.

Expert Analysis

Why the Answer Is Correct

The Lookup activity is designed to read data from a data store and return the results to the pipeline. According to Microsoft documentation, it can execute a query or a stored procedure and return the result as an object in the pipeline context. This allows subsequent activities to access the returned count.

Why the Other Options Are Wrong

The Stored Procedure activity (Option A) executes the procedure but does not return any output values or result sets to the pipeline; it is used solely for side effects. Get Metadata (Option B) retrieves file/folder metadata, not query results. Append Variable (Option C) modifies variables and does not execute database queries.

Community Comment Notes

Comments [1] and [2] correctly cite Microsoft docs stating that the Stored Procedure activity does not allow returning output. Comment [3] reinforces that Lookup is required for SPs with outputs or return values. Comment [5] incorrectly suggests Stored Procedure activity handles results, which contradicts official behavior.

Official Reference

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

Exam Strategy

Remember that if a question requires 'returning' or 'capturing' data from a database operation, choose Lookup. If it only requires 'executing' without needing the result, choose Stored Procedure.

Related Analysis

You have an Azure subscription that contains an Azure Synaps

A-Grade

Note: This question is part of a series of questions that pr

A-Grade

You are building a data flow in Azure Data Factory that upse

A-Grade

← Back to DP-203 Study Guide