none
How to call Sp dynamically in adfv2? RRS feed

  • Question

  • I need to call sp activity where sp name has to be passed as a parameter. Say I have look up activity which will get me the list of sp which has to be called. Output of the look up activity has to be sent to For each activity. inside For each activity we have sp activity which depends on the output of the look up activity which will give the sp name which has to be called and also the parameters which has to be passed to the procedure. Is this possible in adfv2?

    Monday, July 15, 2019 10:44 AM

All replies

  • Hello nvbala.vignesh and thank you for your inquiry.  I am assuming by 'sp' you are referring to stored procedure.  I have determined that assigning both stored procedure name, and stored procedure parameter names, dynamically, is possible.

    As a test, I used:
    {
        "name": "pipeline9",
        "properties": {
            "activities": [
                {
                    "name": "Stored Procedure1",
                    "type": "SqlServerStoredProcedure",
                    "dependsOn": [],
                    "policy": {
                        "timeout": "7.00:00:00",
                        "retry": 0,
                        "retryIntervalInSeconds": 30,
                        "secureOutput": false,
                        "secureInput": false
                    },
                    "userProperties": [],
                    "typeProperties": {
                        "storedProcedureName": {
                            "value": "@concat('[dbo].',pipeline().parameters.procname)",
                            "type": "Expression"
                        },
                        "storedProcedureParameters": {
                            "@pipeline().parameters.param1name": {
                                "value": "2",
                                "type": "Int32"
                            },
                            "@pipeline().parameters.param2name": {
                                "value": "2",
                                "type": "Int32"
                            }
                        }
                    },
                    "linkedServiceName": {
                        "referenceName": "AzureSqlDatabase1",
                        "type": "LinkedServiceReference"
                    }
                }
            ],
            "parameters": {
                "procname": {
                    "type": "string",
                    "defaultValue": "[divide]"
                },
                "param1name": {
                    "type": "string",
                    "defaultValue": "denom"
                },
                "param2name": {
                    "type": "string",
                    "defaultValue": "numer"
                }
            },
            "annotations": []
        }
    }

    This test was only to verify whether expressions are accepted for stored procedure parameter names.
    Monday, July 15, 2019 9:02 PM
    Moderator
  • However, there is one complication, if your stored procedures have different quantity of parameters.  I.e., you have a procedure with 2 parameters, and a procedure with 5 parameters.  That situation is more problematic.

    Assuming all your stored procs have the same quantity of input parameters, you should be okay.  I recommend structuring your for-each items into a JSON like:

    [
      {
        "procname": "firstproc",
        "param1name": "foo",
        "param1type": "Varchar(30)",
        "param1val": "John",
        "param2name": "bar",
        "param2type": "Int32",
        "param2val": 23
      },
      {
        "procname": "secondproc",
        "param1name": "Crowdsize",
        "param1type": "Int32",
        "param1val": "5123",
        "param2name": "temperatureC",
        "param2type": "Decimal(5)",
        "param2val": 28.42
      }
    ]
    (this is just a handwritten JSON to give example form.  Datatypes might not be written correctly.)

    This way you can easily set all the parameter names, types, and values while minimizing the quantity of pipeline parameters / variables.  I also tested setting the type dynamically, but not with all the values above.


    Monday, July 15, 2019 9:26 PM
    Moderator
  • @nvbala.vignesh, have I been able to help you, or do you need more assistance?
    Wednesday, July 17, 2019 5:09 PM
    Moderator
  • @nvbala.vignesh, I haven't heard back from you, so I will now close this issue,
    Thursday, July 18, 2019 5:30 PM
    Moderator