在 Azure 数据工厂中使用 SQL Server 存储过程活动转换数据Transform data by using the SQL Server Stored Procedure activity in Azure Data Factory

适用于:是 Azure 数据工厂是 Azure Synapse Analytics(预览版)APPLIES TO: yesAzure Data Factory yesAzure Synapse Analytics (Preview)

可使用数据工厂管道中的数据转换活动将原始数据转换和处理为预测和见解。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.

备注

如果不熟悉 Azure 数据工厂,请在阅读本文之前,先通读 Azure 数据工厂简介,并学习以下教程:教程:转换数据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.

可以使用存储过程活动调用企业或 Azure 虚拟机 (VM) 中以下数据存储中的存储过程: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 数据库Azure SQL Database
  • Azure Synapse Analytics(以前称为 Azure SQL 数据仓库)Azure Synapse Analytics (formerly Azure SQL Data Warehouse)
  • SQL Server 数据库。SQL Server Database. 如果使用 SQL Server,请在托管数据库的同一计算机上或在可以访问数据库的单独计算机上安装自托管集成运行时。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. 自托管集成运行时是一种以安全托管方式将本地/Azure VM 上的数据源与云服务进行连接的组件。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.

重要

将数据复制到 Azure SQL 数据库或 SQL Server 中时,可以使用 sqlWriterStoredProcedureName 属性将复制活动中的 SqlSink 配置为调用存储过程 。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. 有关属性的详细信息,请参阅以下连接器文章:Azure SQL 数据库SQL ServerFor details about the property, see following connector articles: Azure SQL Database, SQL Server. 不支持在使用复制活动将数据复制到 Azure Synapse Analytics(以前称为 Azure SQL 数据仓库)时调用存储过程。Invoking a stored procedure while copying data into an Azure Synapse Analytics (formerly Azure SQL Data Warehouse) by using a copy activity is not supported. 但是,可使用存储过程活动来调用 SQL 数据仓库中的存储过程。But, you can use the stored procedure activity to invoke a stored procedure in a SQL Data Warehouse.

从 Azure SQL 数据库、SQL Server 或 Azure Synapse Analytics(以前称为 Azure SQL 数据仓库)复制数据时,可以使用 sqlReaderStoredProcedureName 属性将复制活动中的 SqlSource 配置为调用存储过程,以便从源数据库读取数据 。When copying data from Azure SQL Database or SQL Server or Azure Synapse Analytics (formerly 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. 有关详细信息,请参阅以下连接器文章:Azure SQL 数据库SQL ServerAzure Synapse Analytics(以前称为 Azure SQL 数据仓库)For more information, see the following connector articles: Azure SQL Database, SQL Server, Azure Synapse Analytics (formerly Azure SQL Data Warehouse)

语法详细信息Syntax details

下面是用于定义存储过程活动的 JSON 格式: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" }

        }
    }
}

下表描述了其中的 JSON 属性:The following table describes these JSON properties:

属性Property 说明Description 必须Required
namename 活动名称Name of the activity Yes
descriptiondescription 描述活动用途的文本Text describing what the activity is used for No
typetype 对于存储过程活动,活动类型是 SqlServerStoredProcedureFor Stored Procedure Activity, the activity type is SqlServerStoredProcedure Yes
linkedServiceNamelinkedServiceName 引用注册为数据工厂中的链接服务的 Azure SQL 数据库或 Azure Synapse Analytics(以前称为 Azure SQL 数据仓库)或 SQL Server 。Reference to the Azure SQL Database or Azure Synapse Analytics (formerly 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
storedProcedureNamestoredProcedureName 指定要调用的存储过程的名称。Specify the name of the stored procedure to invoke. Yes
storedProcedureParametersstoredProcedureParameters 指定存储过程的参数值。Specify the values for stored procedure parameters. 使用 "param1": { "value": "param1Value","type":"param1Type" } 传递数据源支持的参数值及其类型。Use "param1": { "value": "param1Value","type":"param1Type" } to pass parameter values and their type supported by the data source. 如果需要为参数传递 null,请使用 "param1": { "value": null }(全部小写)。If you need to pass null for a parameter, use "param1": { "value": null } (all lower case). No

参数数据类型映射Parameter data type mapping

为参数指定的数据类型是 Azure 数据工厂类型,该类型映射到正在使用的数据源中的数据类型。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 Synapse Analytics(以前称为 Azure SQL 数据仓库)Azure Synapse Analytics (formerly Azure SQL Data Warehouse) /data-factory/connector-azure-sql-data-warehouse#data-type-mapping-for-azure-sql-data-warehouse/data-factory/connector-azure-sql-data-warehouse#data-type-mapping-for-azure-sql-data-warehouse
Azure SQL 数据库Azure SQL Database /data-factory/connector-azure-sql-database#data-type-mapping-for-azure-sql-database/data-factory/connector-azure-sql-database#data-type-mapping-for-azure-sql-database
OracleOracle /data-factory/connector-oracle#data-type-mapping-for-oracle/data-factory/connector-oracle#data-type-mapping-for-oracle
SQL ServerSQL Server /data-factory/connector-sql-server#data-type-mapping-for-sql-server/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. 但是,数据工厂会将其所有活动运行事件抽取到 Azure Monitor。However, Data Factory pumps all of its activity run events to Azure Monitor. 在数据工厂抽取到 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. 有关详细信息,请参阅使用 Azure Monitor 发警报和监视数据工厂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: