Exam DP-203 topic 2 question 112 discussion

For me the correct answer is C. The store procedure activity doesn't return any data. In the description of the script activity is written that it can be used for : "Run stored procedures. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set. " https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-script

https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-script The script may contain either a single SQL statement or multiple SQL statements that run sequentially. You can use the Script task for the following purposes: Truncate a table in preparation for inserting data. Create, alter, and drop database objects such as tables and views. Re-create fact and dimension tables before loading data into them. Run stored procedures. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set. Save the rowset returned from a query as activity output for downstream consumption.

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

I will go with Stored Procedure.. The requirement is - The solution must minimize development effort. If we go with Script, we will have to write a script which involve development efforts.

"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

C is wrong

Answer is script 100%

B. Stored Procedure Explanation: To execute a stored procedure in an Azure Synapse Analytics dedicated SQL pool and use the returned result set as input for a downstream activity, you should use the Stored Procedure activity in the pipeline. The Stored Procedure activity allows you to execute a stored procedure within the dedicated SQL pool and retrieve the results, which can then be used as input for subsequent activities in the pipeline. This approach minimizes development effort as it directly integrates with the SQL pool and provides a seamless way to execute stored procedures as part of your data processing

Got the feeling the question itself is referring to this GUI: ttps://stackoverflow.com/questions/72642013/pipeline-for-stored-procedure-dedicated-sql-pool So the answer may be A as store procedure

Ans is C. Script

I got this question today in exam 11-jan-2024

The key is 'Output Result set support' which the stored procedure activity does not have. Therefore we have to use a script which supports running stored procedures. https://techcommunity.microsoft.com/t5/azure-data-factory-blog/execute-sql-statements-using-the-new-script-activity-in-azure/ba-p/3239969

C is corret

B. Chat GPT says the given answer is correct. Stored Procedure is specifically designed to execute stored procedures within Azure Synapse Analytics and is the most suitable option for the scenario, minimizing development effort.

The "Script" activity in Azure Data Factory is primarily used to run HDInsight scripts such as Hive, Pig, MapReduce, and Spark. These are typically used for big data processing tasks.

"In Azure Synapse Analytics, you can use the SQL pool Stored Procedure Activity to invoke a stored procedure in a dedicated SQL pool." https://learn.microsoft.com/en-us/azure/synapse-analytics/data-integration/sql-pool-stored-procedure-activity

https://learn.microsoft.com/en-us/azure/synapse-analytics/data-integration/sql-pool-stored-procedure-activity