Execute stored procedure from Azure datafactory

Question

I am trying to execute a stored procedure in an Azure SQL database from an Azure DataFactory V2. The procedure will do some upsert into different tables with data from a flat table. According to MS specifications you need to have a table valued parameter to make such thing, but that couples the pipeline activity to the procedure and to all the models. Is there any way to define the dataset and copy activity so it just executes the stored procedure?

The jsons below are from the arm template:

DataSet:    
{"type": "datasets",
          "name": "AzureSQLProcedureDS",
          "dependsOn": [
            "[parameters('dataFactoryName')]",
            "[parameters('destinationLinkedServiceName')]"
          ],
          "apiVersion": "[variables('apiVersion')]",
          "properties": {
            "type": "AzureSqlTable",
            "linkedServiceName": {
              "referenceName": "[parameters('destinationLinkedServiceName')]",
              "type": "LinkedServiceReference"
            },
            "typeProperties": {
              "tableName": "storedProcedureExecutions"
            }
          }}




    Activity:
    {"name": "ExecuteHarmonizationProcedure",
                    "description": "Executes the procedure that Harmonizes the Data",
                    "type": "Copy",
                    "inputs": [
                      {
                        "referenceName": "[parameters('destinationDataSetName')]",
                        "type": "DatasetReference"
                      }
                    ],
                    "outputs": [
                      {
                        "referenceName": "AzureSQLProcedureDS",
                        "type": "DatasetReference"
                      }
                    ],
                    "typeProperties": {
                      "source": {
                        "type": "SqlSink"
                      },
                      "sink": {
                        "type": "SqlSink",
                        //"SqlWriterTableType": "storedProcedureExecutionsType",
                        "SqlWriterStoredProcedureName": "@Pipeline().parameters.procedureName",
                        "storedProcedureParameters": {
                          "param1": {
                            "value": "call from adf" 
                          }
                        }
                      }
                    }
}

Any help would be appreciated considering that MS doesn't provide so much help for this subject.

Answer 1

Following the advice of @Martin we managed to make the execution work. Below are what we did:

1. Create dummy table in SQL:

   CREATE TABLE [dbo].[dummyTable]( [col1] [NVARCHAR](100) NULL)
   

2. Create a stored procedure:

   CREATE PROCEDURE [dbo].[sp_testHarmonize]
        @param1 NVARCHAR(200)
   AS
   BEGIN
       INSERT INTO storedProcedureExecutions
       VALUES (@param1, GETDATE());
   END
   

3. Dataset for the stored procedure:

   {
       "type": "datasets",
       "name": "[parameters('dummySQLTableDataSet')]",
       "dependsOn": ["[parameters('dataFactoryName')]",
       "[parameters('datalakeLinkedServiceName')]"],
       "apiVersion": "[variables('apiVersion')]",
       "properties": {
               "type": "AzureSqlTable",
               "linkedServiceName": {
                   "referenceName": "[parameters('databaseLinkedServiceName')]",
                   "type": "LinkedServiceReference"
               },
       "typeProperties": {
               "tableName": "dummyTable"
       }
   }
   

4. Pipeline activity:

   {
       "name": "ExecuteHarmonizationProcedure",
       "dependsOn": [{
           "activity": "CopyCSV2SQL",
           "dependencyConditions": ["Succeeded"]
       }],
       "description": "Executes the procedure that Harmonizes the Data",
       "type": "Copy",
       "inputs": [{
           "referenceName": "[parameters('dummySQLTableDataSet')]",
           "type": "DatasetReference"
       }],
       "outputs": [{
           "referenceName": "[parameters('dummySQLTableDataSet')]",
           "type": "DatasetReference"
       }],
       "typeProperties": {
           "source": {
               "type": "SqlSource",
               "sqlReaderQuery": "@Pipeline().parameters.SQLCommand"
           },
           "sink": {
               "type": "SqlSink"
           }
       }
   }
   

5. Run the pipeline with the following parameter for the SQL command:

   $"EXEC sp_testHarmonize 'call from ADF at {DateTime.Now}'; select top 1 * from dummyTable;"
   

This made it worked, but it looks more as a work around than a direct solution, considering that it is inserted a row on a dummy table. If there isn't any more direct solution, this is the most easy approach.

Answer 2

I'm not sure if I understand the problem correctly, you just want to call a stored procedure from a copy activity?

Doing that is pretty easy, in a copy activity you can define the sqlReaderQuery property inside source. This property lets you enter a t-sql command, so you can do something like this:

 "typeProperties": {
        "source": {
            "type": "SqlSource",
            "sqlReaderQuery": "EXEC sp_Name; select 1 as test"
        },
 . . .

Copy activity always expects a result from the query, so if you only include the call to the stored procedure it doesnt thats why I include the second part of the query.

Replace with the parameters you want to use and thats it.