Call stored procedure using ADF

Question

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

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

2. Stored procedure using user defined table type - Getting error

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"
    }
}

and using below stored procedure

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;

Please help me to resolve the issue.

Answer 1

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)

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.