Question 105432 in Azure Question posted in
The official documentation to get you started can be found here.

Azure Data Factory with SP Activity – Debug and Publish fails

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search