I've created an Azure Data Factory pipeline with one simple Stored Procedure Activity which is supposed to fetch data from a Stored Procedure residing in Azure SQL DB. The Stored Procedure accepts one input parameter. I've published these changes already.

When I click on Validate, I get the below error from where I hardly get any information:

{
  "code": "BadRequest",
  "message": null,
  "target": "pipeline//runid/dcb92f70-0a4b-4be1-943b-5ggn68365tyc",
  "details": null,
  "error": null
} 

When I click on Trigger now, it just says 'Failed to run pipeline' without anymore details.

My pipeline JSON is given below:

{
    "name": "GetPopulationRecordsForAnalysis",
    "properties": {
        "description": "Gets Population Records",
        "activities": [
            {
                "name": "GetPopulationRecords",
                "type": "SqlServerStoredProcedure",
                "dependsOn": [],
                "policy": {
                    "timeout": "7.00:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "storedProcedureName": "[dbo].[usp_GetPopulationRecords]",
                    "storedProcedureParameters": {
                        "@countryID": {
                            "value": "48",
                            "type": "Int64"
                        }
                    }
                },
                "linkedServiceName": {
                    "referenceName": "AzureSqlLinkedService",
                    "type": "LinkedServiceReference"
                }
            }
        ],
        "annotations": [],
        "lastPublishTime": "2022-08-02T13:37:27Z"
    },
    "type": "Microsoft.DataFactory/factories/pipelines"
}

What am I doing wrong here?

CodePudding user response：

I have figured out the issue now. The first mistake that I was doing is that I was giving the complete SP name with schema, '['character and all, usp_GetPopulationRecords works just fine. Second is that I was adding an extra '@' character before my Input parameter like how we do while running in SQL Server. That is not required here, only countryIDworks fine. Hope my answer helps.

CodePudding user response：

When we connect to the Linked service in the Settings tab, “Stored Procedure name” dropdown will populate the names of the Stored Procedures present in the Database. We should select the required Stored procedure and Upon clicking Import it will display parameter Name, Type and Value (supply Value) as below:

enter image description here

We need not give ‘@’ before parameter name. Corresponding JSON will look below:

enter image description here

This will be Validated successfully in ADF.