概要
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 のパイプラインを実行
