Calling Azure SQL DW Stored Procedure from Azure Data Factory

Question

I am getting the following error when trying to run a stored procedure in an Azure SQL Datawarehouse.

Activity 'SprocActivitySample' contains an invalid Dataset reference 'Destination-SQLDW-nna'. This dataset is pointing to Azure SQL DW and stored procedure is in it.

Here is the entire code.

{
    "name": "SprocActivitySamplePipeline",
    "properties": {
        "activities": [
            {
                "type":"SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "DailyImport",
                    "storedProcedureParameters": {
                        "DateToImportFor": "$$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', SliceStart)"
                    }
                },
                "outputs": [
                    {
                        "name": "Destination-SQLDW-nna"
                    }
                ],
                "scheduler": {
                    "frequency": "Day",
                    "interval": 1
                },
                "name": "SprocActivitySample"
            }
        ],
         "start": "2017-01-01T00:00:00Z",
         "end": "2017-02-20T05:00:00Z",
        "isPaused": true
    }
}

Martin Esteban Zurita Martin Esteban Zurita · Accepted Answer · 2017-12-26T11:32:13

I'm afraid that Azure Sql Data Warehouse does not support table-valued parameters in stored procedures.

Read more about it here: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-stored-procedures

If you find a workaround for it please share! I couldnt find any.

Also it would be good if you can post the dataset json so we can try to find any errors on it.

Cheers!

Calling Azure SQL DW Stored Procedure from Azure Data Factory

2 Answers