使用ADF调用存储过程 [英] Call stored procedure using ADF

问题描述

我正在使用ADF加载SQL Server表，并且插入结束后，我必须使用以下方法进行少量操作

I am loading SQL server table using ADF and after insertion is over, I have to do little manipulation using below approach

1. 触发器(插入后)-SQL Server无法检测到我使用ADF推送的插入记录.. **Seems to be a bug** .

1. Trigger (After insert) - Failed, SQL server not able to detect inserted record that I push using ADF.. **Seems to be a bug**.

使用用户定义的表类型的存储过程-出现错误

Stored procedure using user defined table type - Getting error

错误号"156".来自数据库执行的错误消息:不正确 关键字"select"附近的语法.必须声明表变量 "@a".

Error Number '156'. Error message from database execution : Incorrect syntax near the keyword 'select'. Must declare the table variable "@a".

我已经在管道下面创建了

I have created below pipeline

{
    "name": "CopyPipeline-xxx",
    "properties": {
        "activities": [
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "AzureDataLakeStoreSource",
                        "recursive": false
                    },
                    "sink": {
                        "type": "SqlSink",
                        "sqlWriterStoredProcedureName": "sp_xxx",
                        "storedProcedureParameters": {
                            "stringProductData": {
                                "value": "str1"
                            }
                        },
                        "writeBatchSize": 0,
                        "writeBatchTimeout": "00:00:00"
                    },
                    "translator": {
                        "type": "TabularTranslator",
                        "columnMappings": "col1:col1,col2:col2"
                    }
                },
                "inputs": [
                    {
                        "name": "InputDataset-3jg"
                    }
                ],
                "outputs": [
                    {
                        "name": "OutputDataset-3jg"
                    }
                ],
                "policy": {
                    "timeout": "1.00:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst",
                    "style": "StartOfInterval",
                    "retry": 3,
                    "longRetry": 0,
                    "longRetryInterval": "00:00:00"
                },
                "scheduler": {
                    "frequency": "Hour",
                    "interval": 8
                },
                "name": "Activity-0-xxx_csv->[dbo]_[xxx_staging]"
            }
        ],
        "start": "2017-01-09T21:48:53.348Z",
        "end": "2099-12-30T18:30:00Z",
        "isPaused": false,
        "hubName": "hub",
        "pipelineMode": "Scheduled"
    }
}

并使用以下存储过程

create procedure [dbo].[sp_xxx] @xxx1 [dbo].[ut_xxx] READONLY, @str1 varchar(100) AS

MERGE xxx_dummy AS a
USING @xxx1 AS b
ON (a.col1 = b.col1) 
WHEN NOT MATCHED 
    THEN INSERT(col1, col2) 
    VALUES(b.col1, b.col2)
WHEN MATCHED 
    THEN UPDATE SET a.col2 = b.col2;

请帮助我解决问题.

推荐答案

我可以重现您的第一个错误.使用Azure数据工厂(ADF)插入SQL Server表似乎使用了批量插入方法(类似于BULK INSERT，bcp，SSIS等)，默认情况下，这些方法不会触发触发器:

I can reproduce your first error. Inserting to a SQL Server table with Azure Data Factory (ADF) appears to use a bulk insert method (similar to BULK INSERT, bcp, SSIS etc) and by default these methods do not fire triggers:

insert bulk [dbo].[testADF] ([col1] Int, [col2] Int, [col3] Int, [col4] Int) 
with (TABLOCK, CHECK_CONSTRAINTS)

使用bcp，BULK INSERT时，有一个标志可以更改为火警触发器"，但似乎无法更改ADF的此设置.解决方法是将逻辑从触发器移到存储的过程中.

With bcp, BULK INSERT there is a flag to change to say 'fire triggers' but it appears there is no way to change this setting for ADF. As a workaround, move the logic from your trigger into the stored proc.

如果您认为此标志很重要，请考虑创建一个反馈项

If you believe this flag is important, consider creating a feedback item.

这篇关于使用ADF调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！