Transform data by using the SQL Server Stored Procedure activity in Azure Data Factory or Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

You use data transformation activities in a Data Factory or Synapse pipeline to transform and process raw data into predictions and insights. The Stored Procedure Activity is one of the transformation activities that pipelines support. This article builds on the transform data article, which presents a general overview of data transformation and the supported transformation activities.

Note

If you are new to Azure Data Factory, read through Introduction to Azure Data Factory and do the tutorial: Tutorial: transform data before reading this article. To learn more about Synapse Analytics, read What is Azure Synapse Analytics.

You can use the Stored Procedure Activity to invoke a stored procedure in one of the following data stores in your enterprise or on an Azure virtual machine (VM):

  • Azure SQL Database
  • Azure Synapse Analytics
  • SQL Server Database. If you are using SQL Server, install Self-hosted integration runtime on the same machine that hosts the database or on a separate machine that has access to the database. Self-Hosted integration runtime is a component that connects data sources on-premises/on Azure VM with cloud services in a secure and managed way. See Self-hosted integration runtime article for details.

Important

When copying data into Azure SQL Database or SQL Server, you can configure the SqlSink in copy activity to invoke a stored procedure by using the sqlWriterStoredProcedureName property. For details about the property, see following connector articles: Azure SQL Database, SQL Server. Invoking a stored procedure while copying data into an Azure Synapse Analytics by using a copy activity is not supported. But, you can use the stored procedure activity to invoke a stored procedure in Azure Synapse Analytics.

When copying data from Azure SQL Database or SQL Server or Azure Synapse Analytics, you can configure SqlSource in copy activity to invoke a stored procedure to read data from the source database by using the sqlReaderStoredProcedureName property. For more information, see the following connector articles: Azure SQL Database, SQL Server, Azure Synapse Analytics

Syntax details

Here is the JSON format for defining a Stored Procedure Activity:

{
    "name": "Stored Procedure Activity",
    "description":"Description",
    "type": "SqlServerStoredProcedure",
    "linkedServiceName": {
        "referenceName": "AzureSqlLinkedService",
        "type": "LinkedServiceReference"
    },
    "typeProperties": {
        "storedProcedureName": "usp_sample",
        "storedProcedureParameters": {
            "identifier": { "value": "1", "type": "Int" },
            "stringData": { "value": "str1" }

        }
    }
}

The following table describes these JSON properties:

Property Description Required
name Name of the activity Yes
description Text describing what the activity is used for No
type For Stored Procedure Activity, the activity type is SqlServerStoredProcedure Yes
linkedServiceName Reference to the Azure SQL Database or Azure Synapse Analytics or SQL Server registered as a linked service in Data Factory. To learn about this linked service, see Compute linked services article. 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

Parameter data type mapping

The data type you specify for the parameter is the internal service type that maps to the data type in the data source you are using. You can find the data type mappings for your data source described in the connectors documentation. For example:

Next steps

See the following articles that explain how to transform data in other ways: