none
Copy activity - sink stored procedure parameter value ignored

    Question

  • After adding a copy activity, and specifying a stored procedure to use - I want to add a stored procedure parameter:

    Running the activity, I get an error saying that there is no value specified for the stored procedure parameter projectId.

    Looking at the generated JSON code for the copy activity, I can indeed see that there is no value set for the parameter:

    "sink": {
        "type": "SqlSink",
        "writeBatchSize": 10000,
        "sqlWriterStoredProcedureName": "[dbo].[CreateAllocation]",
        "sqlWriterTableType": "AllocationType",
        "storedProcedureParameters": {
            "projectId": {
                "type": "String"
            }
        }
    },
    "enableStaging": false,
    "cloudDataMovementUnits": 0

    Setting the value of the parameter by clicking the Advanced tab on the copy activity, and pasting the following JSON makes it work:

    "sink": {
        "type": "SqlSink",
        "writeBatchSize": 10000,
        "sqlWriterStoredProcedureName": "[dbo].[CreateAllocation]",
        "sqlWriterTableType": "AllocationType",
        "storedProcedureParameters": {
            "projectId": {
                "type": "String",
                "value": "200"
            }
        }
    },
    "enableStaging": false,
    "cloudDataMovementUnits": 0

    I've also tried to connect the data factory to git, and making a commit that sets the value of the stored procedure parameter - but no luck. As soon as the copy activity has loaded, it seems like it strips out the value set for the parameter.

    How come that I can't set the value of the stored procedure parameter, without using the Advanced tab to override the activity template?




    Saturday, March 24, 2018 6:39 PM

Answers

  • Hi,

    Thank you for reporting this issue. It was indeed a bug and we have fixed it.

    Sorry for the trouble.

    • Marked as answer by ebb1337 Tuesday, March 27, 2018 9:36 PM
    Tuesday, March 27, 2018 1:27 AM

All replies

  • Hi,

    If you want to pass a persisted value for stored procedure input, create a table with SP name and parameter value. Create another pipeline that gets the SP parameter value and passes that as input to invoked pipeline.

    Just like in this example - http://www.allaboutmssql.com/2018/02/how-to-copy-multiple-tables-to-azure.html (List of tables from query output is passed as input to invoked pipeline).


    sathya - My blog ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, March 24, 2018 10:08 PM
  • Hi Sathya,

    Thanks for taking the time to writing a reply.

    The example from your blog, does not use stored procedure as a sink. I've tried changing the value for the stored procedure parameter "200" to something dynamic, but without luck.

    No matter what I do, the value seems to be "set" when viewing it in the visual designer - but looking at the code or refreshing the page, the value is no longer set.

    The only thing that works, is pasting a chunk of JSON into the "Advanced" tab of the copy activity, setting the value of the stored procedure parameter.


    • Edited by ebb1337 Sunday, March 25, 2018 9:16 AM
    Sunday, March 25, 2018 9:05 AM
  • Good morning,

    So, looks like it's not just me.  It's another 'feature' that crept in with the latest changes that broke all of my pipelines again!  I had the values for parameters set to variables (e.g., @{item().ID}).  Today, those values were wiped out.  If I try to add them back in, they are not taken into account!  I'm getting the following message:

    {
        "errorCode": "2200",
        "message": "ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'Value' is invalid: 'Value is required property of stored procedure parameters\r\nParameter name: Value'.,Source=,''Type=System.ArgumentException,Message=Value is required property of stored procedure parameters\r\nParameter name: Value,Source=Microsoft.DataTransfer.ClientLibrary,'",
        "failureType": "UserError",
        "target": "Load to Raw Tbl"
    }

    This is another bug Microsoft needs to address ASAP!

    Thanks,

    Polina


    Monday, March 26, 2018 6:16 PM
  • Hi,

    Thank you for reporting this issue. It was indeed a bug and we have fixed it.

    Sorry for the trouble.

    • Marked as answer by ebb1337 Tuesday, March 27, 2018 9:36 PM
    Tuesday, March 27, 2018 1:27 AM