locked
Dynamic parameters/variables for Stored Proc call with @TVP Parameter RRS feed

  • Question

  • Hello!

    I have a more general question to see if anyone has done this before with ADFv2. I need to make a "generic" call to one of several Stored Procs, all with a @TVP parameter. The Stored Proc that I will call will depend on the type of data; therefore, I have to decide on the fly what type of data and which Stored Proc to call with the TVP Parameter. The following is an example of one that I have hard coded for now just to get the logic working in my expressions.

     

    "sink": {

    "type": "SqlSink",

    "writeBatchSize": 10000,

    "sqlWriterStoredProcedureName": "[custom].[usp_CustomMasterData_TvpMergeV3]",

    "sqlWriterTableType": "custom.CustomMasterDataTypeV3"

    },

     

    I guess my question is "can I send in sqlWriterStoredProcedureName and sqlWriterTableType as parameters (or variables) for the SqlSink? I really appreciate any thoughts as I have to complete this demo by end of June; I need to do this dynamic calling of stored procs in that demo.

     

    Thanks all!

    Mike Kiser


    Mike Kiser

    Wednesday, June 19, 2019 2:49 PM

Answers

  • Hi Mike,

    Yes you can pass the value of sqlWriterStoredProcedureName as a parameter from ADF V2. You can do so by clicking on "Add dynamic content" as shown below.

    Quick Tip - Try to chain this copy activity to a set variable activity to make sure that the variable you want to pass is populated with some value.

    Hope this helps.



    Thursday, June 20, 2019 8:27 AM

All replies

  • Hi Mike,

    Yes you can pass the value of sqlWriterStoredProcedureName as a parameter from ADF V2. You can do so by clicking on "Add dynamic content" as shown below.

    Quick Tip - Try to chain this copy activity to a set variable activity to make sure that the variable you want to pass is populated with some value.

    Hope this helps.



    Thursday, June 20, 2019 8:27 AM
  • Awesome, Chirag! I am going to start work on it now. Thank you so much!!

    Mike


    Mike Kiser

    Thursday, June 20, 2019 2:00 PM
  • Glad to be of help Mike :)
    Friday, June 21, 2019 7:28 AM