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

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

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.

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 SQL Data Warehouse
  • 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 SQL Data Warehouse by using a copy activity is not supported. But, you can use the stored procedure activity to invoke a stored procedure in a SQL Data Warehouse.

When copying data from Azure SQL Database or SQL Server or Azure SQL Data Warehouse, 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 SQL Data Warehouse

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 SQL Data Warehouse 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 Azure Data Factory 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 in the connectors area. Some examples are

Data Source Data Type Mapping
Azure SQL Data Warehouse https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse#data-type-mapping-for-azure-sql-data-warehouse
Azure SQL Database https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database#data-type-mapping-for-azure-sql-database
Oracle https://docs.microsoft.com/en-us/azure/data-factory/connector-oracle#data-type-mapping-for-oracle
SQL Server https://docs.microsoft.com/en-us/azure/data-factory/connector-sql-server#data-type-mapping-for-sql-server

Error info

When a stored procedure fails and returns error details, you can't capture the error info directly in the activity output. However, Data Factory pumps all of its activity run events to Azure Monitor. Among the events that Data Factory pumps to Azure Monitor, it pushes error details there. You can, for example, set up email alerts from those events. For more info, see Alert and Monitor data factories using Azure Monitor.

Next steps

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