You have an Azure Synapse Analytics dedicated SQL pool.
You need to create a pipeline that will execute a stored procedure in the dedicated SQL pool and use the returned result set as the input for a downstream activity. The solution must minimize development effort.
Which type of activity should you use in the pipeline?
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, the Script activity should be used. The Script activity can run SQL statements, including stored procedures, and handle the returned result set for downstream consumption. This approach minimizes development effort by allowing the direct execution of stored procedures and retrieval of result sets within the same activity.
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
I also think this one is correct. One of the things script activity can do is "...Save the rowset returned from a query as activity output for downstream consumption." which is pretty much what is needed here. This is not viable with 'execute SP' activity as it doesn't cannot return any data.
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.
"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
"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
What did you put for your answer?
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.
False, finally I've found the link, it's C: https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-script