Azure Data Factory にて Azure SQL Database のストアードプロシージャアクティビティをパラメータを動的に設定してリターン値を取得する方法

## 概要

Azure Data Factory にて Azure SQL Database のストアードプロシージャアクティビティをパラメータを動的に設定してリターン値を取得する方法を共有します。次表に示すようなパラメータに基づきスクリプトアクティビティを実行することでストアードプロシージャの実行結果を取得します。本記事では検証コードと結果を記述します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/575f712a-d763-6781-cbc1-1a231b8e19f0.png)


| #    | パラメータ名        | 概要                                                   | 設定値例                                                     |
| ---- | ------------------- | ------------------------------------------------------ | ------------------------------------------------------------ |
| 1    | sp_name             | ストアードプロシージャ名                               | dbo.StoredProcedure001                                       |
| 2    | sp_paras            | ストアードプロシージャアクティビティのパラメータ       | [{"name":"Param1","type":"String","value":"Hello  ","direction":"Input"},{"name":"Param2","type":"String","value":"World  !","direction":"Input"},{"name":"OutputValues","type":"String","value":"","direction":"Output","size":"200"}] |
| 3    | sp_paras_str        | ストアードプロシージャを実行する際のパラメータの文字例 | @Param1, @Param2, @OutputValues OUTPUT                       |
| 4    | return_para_name_01 | アウトプットされるパラメータ名                         | OutputValues                                                 |


## 検証コードと実行結果

### 1. ストアードプロシージャを Azure SQL Database に配置

`Param1`パラメータと`Param2`パラメータの文字を結合して返すストアードプロシージャを Azure SQL Database にデプロイします。

```sql
-- DROP  PROCEDURE dbo.StoredProcedure001;
CREATE PROCEDURE dbo.StoredProcedure001
    @Param1 VARCHAR(50),
    @Param2 VARCHAR(50),
	@OutputValues VARCHAR(200) OUTPUT
AS
BEGIN
    SET @OutputValues =@Param1 + @Param2;
END;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/1af7ff6e-6d93-d783-8bc2-7f5fcd3233bc.png)

`Hello `と`World !`を渡すと、`Hello World !`を返します。

```sql
DECLARE @abc nvarchar(200)

EXEC dbo.StoredProcedure001 'Hello ','World !',@abc OUTPUT

SELECT @abc
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4d477566-e11c-e68c-d16b-25a156190d8f.png)


### 2.  Azure Data Factory にてストアードプロシージャをコール後にアウトプット結果を戻り値とするパイプラインを作成

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a05cdb4e-efe9-3f95-a837-573225570995.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5cf36ca1-9cf2-e686-469f-691ca0d22d7a.png)

<details>
<summary>パイプラインの定義</summary>

```json
{
    "name": "pipeline",
    "properties": {
        "activities": [
            {
                "name": "Script1",
                "type": "Script",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "linkedServiceName": {
                    "referenceName": "sqldb__pj1",
                    "type": "LinkedServiceReference"
                },
                "typeProperties": {
                    "scripts": [
                        {
                            "parameters": {
                                "value": "@pipeline().parameters.sp_paras",
                                "type": "Expression"
                            },
                            "type": "Query",
                            "text": {
                                "value": "@concat(\n    'EXEC '\n    ,pipeline().parameters.sp_name\n    ,' '\n    ,pipeline().parameters.sp_paras_str\n)",
                                "type": "Expression"
                            }
                        }
                    ],
                    "scriptBlockExecutionTimeout": "02:00:00"
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Script1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "pipelineReturnValue",
                    "value": [
                        {
                            "key": "return_value",
                            "value": {
                                "type": "Expression",
                                "content": "@activity('Script1').output.outputParameters[pipeline().parameters.return_para_name]"
                            }
                        }
                    ],
                    "setSystemVariable": true
                }
            }
        ],
        "parameters": {
            "sp_name": {
                "type": "string",
                "defaultValue": "dbo.StoredProcedure001 "
            },
            "sp_paras": {
                "type": "array",
                "defaultValue": [
                    {
                        "name": "Param1",
                        "type": "String",
                        "value": "Hello ",
                        "direction": "Input"
                    },
                    {
                        "name": "Param2",
                        "type": "String",
                        "value": "World !",
                        "direction": "Input"
                    },
                    {
                        "name": "OutputValues",
                        "type": "String",
                        "value": "",
                        "direction": "Output",
                        "size": "200"
                    }
                ]
            },
            "sp_paras_str": {
                "type": "string",
                "defaultValue": "@Param1, @Param2, @OutputValues OUTPUT"
            },
            "return_para_name": {
                "type": "string",
                "defaultValue": "OutputValues"
            }
        },
        "annotations": []
    }
}
```

</details>


### 3. 2 のパイプラインの実行とそのリターン値を取得するパイプラインを作成

`@activity('Execute Pipeline1').output.pipelineReturnValue.return_value`という式にてストアードプロシージャの実行結果を取得します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/3881a6eb-cdc1-0436-b7be-2baf44255356.png)

<details>
<summary>パイプラインの定義</summary>

```json
{
    "name": "parent_pipeline",
    "properties": {
        "activities": [
            {
                "name": "Execute Pipeline1",
                "type": "ExecutePipeline",
                "dependsOn": [],
                "userProperties": [],
                "typeProperties": {
                    "pipeline": {
                        "referenceName": "pipeline",
                        "type": "PipelineReference"
                    },
                    "waitOnCompletion": true
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Execute Pipeline1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "test",
                    "value": {
                        "value": "@activity('Execute Pipeline1').output.pipelineReturnValue.return_value",
                        "type": "Expression"
                    }
                }
            }
        ],
        "variables": {
            "test": {
                "type": "String"
            }
        },
        "annotations": []
    }
}
```


</details>

### 4. 3 のパイプラインを実行

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c80b361b-3f2f-f390-aead-df19f6c82988.png)