Edit

Share via

Transform data by using the SQL Server Stored Procedure activity in Azure Data Factory or 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 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

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.

If you call a stored procedure with output parameters using stored procedure activity, following error occurs.

Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 201. Error Message: Procedure or function 'sp_name' expects parameter '@output_param_name', which was not supplied.

Create a Stored Procedure activity with UI

To use a Stored Procedure activity in a pipeline, complete the following steps:

  1. Search for Stored Procedure in the pipeline Activities pane, and drag a Stored Procedure activity to the pipeline canvas.

  2. Select the new Stored Procedure activity on the canvas if it is not already selected, and its Settings tab, to edit its details.

    Shows the UI for a Stored Procedure activity.

  3. Select an existing or create a new linked service to an Azure SQL Database, Azure Synapse Analytics, or SQL Server.

  4. Choose a stored procedure, and provide any parameters for its execution.

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:

Related content

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