Edit

Share via

Transform data by using SQL pool stored procedure activity in Azure Synapse Analytics

  • Article

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

You use data transformation activities in a pipeline to transform and process raw data into predictions and insights. This article builds on the transform data article, which presents a general overview of data transformation and the supported transformation activities.

In Azure Synapse Analytics, you can use the SQL pool Stored Procedure Activity to invoke a stored procedure in a dedicated SQL pool.

Syntax details

The following settings are supported in SQL pool stored procedure activity:

Property Description Required
name Name of the activity Yes
description Text describing what the activity is used for No
type For SQL pool stored procedure activity, the activity type is SqlPoolStoredProcedure Yes
sqlPool Reference to a dedicated SQL pool in the current Azure Synapse workspace. Yes
storedProcedureName Specify the name of the stored procedure to invoke. Yes
storedProcedureParameters Specify the values for stored procedure parameters. Use "param1": { "value": "param1Value","type":"param1Type" } to pass parameter values and their type supported by the data source. If you need to pass null for a parameter, use "param1": { "value": null } (all lower case). No

Example:

{
    "name": "SQLPoolStoredProcedureActivity",
    "description":"Description",
    "type": "SqlPoolStoredProcedure",
    "sqlPool": {
        "referenceName": "DedicatedSQLPool",
        "type": "SqlPoolReference"
    },
    "typeProperties": {
        "storedProcedureName": "usp_sample",
        "storedProcedureParameters": {
            "identifier": { "value": "1", "type": "Int" },
            "stringData": { "value": "str1" }

        }
    }
}

Next steps