概要

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

#	パラメータ名	概要	設定値例
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 にデプロイします。

-- 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;

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

DECLARE @abc nvarchar(200)

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

SELECT @abc

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

パイプラインの定義

{
    "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": []
    }
}

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

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

パイプラインの定義

{
    "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": []
    }
}

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