从Azure数据工厂执行存储过程 [英] Execute stored procedure from Azure datafactory

问题描述

我正在尝试从Azure DataFactory V2在Azure SQL数据库中执行存储过程.该过程将使用平面表中的数据对不同的表进行一些升级.根据

I am trying to execute a stored procedure in an Azure SQL database from an Azure DataFactory V2. The procedure will do some upsert into different tables with data from a flat table. According to MS specifications you need to have a table valued parameter to make such thing, but that couples the pipeline activity to the procedure and to all the models. Is there any way to define the dataset and copy activity so it just executes the stored procedure?

下面的json来自arm模板:

The jsons below are from the arm template:

DataSet:    
{"type": "datasets",
          "name": "AzureSQLProcedureDS",
          "dependsOn": [
            "[parameters('dataFactoryName')]",
            "[parameters('destinationLinkedServiceName')]"
          ],
          "apiVersion": "[variables('apiVersion')]",
          "properties": {
            "type": "AzureSqlTable",
            "linkedServiceName": {
              "referenceName": "[parameters('destinationLinkedServiceName')]",
              "type": "LinkedServiceReference"
            },
            "typeProperties": {
              "tableName": "storedProcedureExecutions"
            }
          }}




    Activity:
    {"name": "ExecuteHarmonizationProcedure",
                    "description": "Executes the procedure that Harmonizes the Data",
                    "type": "Copy",
                    "inputs": [
                      {
                        "referenceName": "[parameters('destinationDataSetName')]",
                        "type": "DatasetReference"
                      }
                    ],
                    "outputs": [
                      {
                        "referenceName": "AzureSQLProcedureDS",
                        "type": "DatasetReference"
                      }
                    ],
                    "typeProperties": {
                      "source": {
                        "type": "SqlSink"
                      },
                      "sink": {
                        "type": "SqlSink",
                        //"SqlWriterTableType": "storedProcedureExecutionsType",
                        "SqlWriterStoredProcedureName": "@Pipeline().parameters.procedureName",
                        "storedProcedureParameters": {
                          "param1": {
                            "value": "call from adf" 
                          }
                        }
                      }
                    }
}

考虑到MS不能为该主题提供太多帮助，任何帮助将不胜感激.

Any help would be appreciated considering that MS doesn't provide so much help for this subject.

推荐答案

我不确定我是否正确理解问题，您只是想从复制活动中调用存储过程?

I'm not sure if I understand the problem correctly, you just want to call a stored procedure from a copy activity?

做到这一点非常容易，在复制活动中，您可以在源代码中定义sqlReaderQuery属性.该属性使您可以输入t-sql命令，因此您可以执行以下操作:

Doing that is pretty easy, in a copy activity you can define the sqlReaderQuery property inside source. This property lets you enter a t-sql command, so you can do something like this:

 "typeProperties": {
        "source": {
            "type": "SqlSource",
            "sqlReaderQuery": "EXEC sp_Name; select 1 as test"
        },
 . . .

复制活动始终期望查询结果，因此，如果仅包括对存储过程的调用，那不是我为什么要包括查询第二部分的原因.

Copy activity always expects a result from the query, so if you only include the call to the stored procedure it doesnt thats why I include the second part of the query.

替换为您要使用的参数，就这样.

Replace with the parameters you want to use and thats it.

这篇关于从Azure数据工厂执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！