locked
Calling a stored procedure after copying from CSV to Azure DB table RRS feed

  • Question

  • I have a need to copy the data from 3 CSV files into 3 staging tables, and then call a stored procedure to move the data from the staging tables into the line of business tables.
    Copying the data from the CSV files into the correct staging tables isn't a problem.

    But how to call a stored procedure once the third saging table has been populated successfully?

    From the Azure documentation it looks like I add a new Activity of type SqlServerStoredProcedure.  When I follow the example in the docs and try to deploy the ARM template I get an error message saying:

    "New-AzureRmResourceGroupDeployment : 15:55:54 - Error: Code=InvalidTemplate; Message=Deployment template parse failed: 'Could not find member 'typeProperties'
    on object of type 'TemplateResource'." not found."

    The section of my ARM template that refers to is:

            {
              "name": "[variables('activity_call_proc')]",
              "type": "SqlServerStoredProcedure",
              "apiVersion": "2017-09-01-preview",
              "typeProperties": {
                "storedProcedureName": "[parameters('db_proc')]"
              },
              "linkedServiceName": {
                "referenceName": "[variables('azureSqlLinkedServiceName')]",
                "type": "LinkedServiceReference"
              }
            }

    The typeProperties clearly *are* there, so the error message must mean something else.

    How do I get my pipeline to call the stored procedure on the Azure SQL db referenced in the linkedServiceName?

    Monday, December 4, 2017 4:01 PM

Answers

  • OK, I found the problem.

    I was passing in the name of the stored proc in square brackets, and it seems that ADF doesn't like that.  When I removed those from the parameter passed in the ARM template deployed OK.

    • Marked as answer by JohnNev Tuesday, December 5, 2017 3:50 PM
    Tuesday, December 5, 2017 3:49 PM