none
ADFv2 - Custom Activity - pass parameter - Stored Procedure Activity

    Question

  • I am working on a pipeline in Azure Data Factory V2.

    I want to pass a string from a custom batch app activity to a stored procedure activity via the input parameter of the stored procedure.  I am trying to get the data from stdout.txt of the custom activity but I am open to other ideas.  According to the documentation at https://docs.microsoft.com/en-us/azure/data-factory/transform-data-using-dotnet-custom-activity:

    If you would like to consume the content of stdout.txt in downstream activities, you can get the path to the stdout.txt file in expression "@activity('MyCustomActivity').output.outputs[0]"  

    I have put this expression in the Stored Procedure parameter value and tried many permutations (including output.value instead of output.outputs[0] as suggested in another blog post) with no success.  In case it matters, the target database is SQL Server on an Azure VM.

    Does anyone have a suggestion, or a working example they could share?

    Thursday, April 26, 2018 9:15 PM

Answers

  • you can write the output from custom activity to outputs.json file in the same directory as your custom activity app/ executable. 

    Example from a .NET code in Custom Activity: 
    File.WriteAllText("outputs.json", "mycustom value");

    In ADF, you can access this value of outputs.json (which in this case is 'mycustom value') using:
    @activity('CustomActivityName').output.customOutput

    If you pass Key-value pairs in outputs.json, then you can simply access it using @activity('CustomActivityName').output.customOutput.KeyName

    • Proposed as answer by Sudhir Rawat Sunday, June 3, 2018 6:00 PM
    • Marked as answer by Polly Herr Tuesday, July 3, 2018 6:13 PM
    Friday, June 1, 2018 4:01 PM

All replies

  • It seems you are trying to pass the path (returned by @activity('MyCustomActivity').output.outputs[0]) for the output file (stdout.txt) generated by your custom activity.  What you might want to do is create a LookUp Activity to Loop Values in stdout.txt using its path and pass those as parameters to the Stored Proc.  Hope this helps!  
    • Proposed as answer by SP Yogi Friday, April 27, 2018 4:30 PM
    Friday, April 27, 2018 4:30 PM
  • Thanks so much for your answer.  I tried the LookUp Activity idea, but couldn't get it to work.  Would you please provide some sample code showing how to use Lookup to read a string from the stdout.txt file and then pass it as a parameter to a Stored Procedure Activity?
    Friday, April 27, 2018 7:29 PM
  • Sorry don't have sample code, but here is what you should try.  Add a lookup activity to use "Storage Linked Service" associated with Batch and use the output file path (as parameter) from the previous step, i.e. custom batch activity.  Depending on whether you are expecting one or multiple values in stdout.txt you will need to set firstrowonly and follow up with a foreach activity to process multiple values and invoke the SP multiple times.  You can start with the Lookup activity example
    Friday, April 27, 2018 8:00 PM
  • you can write the output from custom activity to outputs.json file in the same directory as your custom activity app/ executable. 

    Example from a .NET code in Custom Activity: 
    File.WriteAllText("outputs.json", "mycustom value");

    In ADF, you can access this value of outputs.json (which in this case is 'mycustom value') using:
    @activity('CustomActivityName').output.customOutput

    If you pass Key-value pairs in outputs.json, then you can simply access it using @activity('CustomActivityName').output.customOutput.KeyName

    • Proposed as answer by Sudhir Rawat Sunday, June 3, 2018 6:00 PM
    • Marked as answer by Polly Herr Tuesday, July 3, 2018 6:13 PM
    Friday, June 1, 2018 4:01 PM
  • Hi, I tried this approach but found that the customOutput is unrecognized. Are you sure this is the way to go?
    Tuesday, June 12, 2018 5:53 PM
  • Make sure you write the results into outputs.json from your application running in custom activity? You can verify the output of custom activity in ADF Monitoring UI. It should contain a customOutput property with the value passed from your application. This property is missing in output when you have not written any value into outputs.json.
    Sunday, July 1, 2018 4:56 PM
  • Abhishek - thanks very much for your help.  This simplified my code quite a bit.

    There is no mention of using outputs.json and output.customOutput as you suggested here in any of the Azure Data Factory V2 Batch Service application documentation.  It would be helpful to add it.

     
    Tuesday, July 3, 2018 6:24 PM
  • Hi all, 

    I have tried this approach and can't find the customOutput from the activity's output in ADF monitoring UI.

    I am using Cloud Services Windows Server 2016 for the batch pool and created the outputs.json in different location but none of them seem to work. The ConsoleApp1.exe is the app/executable.

    Files on node from azure batch task:

    stderr.txt
    stdout.txt
    wd\test.zip
    wd\test\ConsoleApp1.exe
    wd\test\ConsoleApp1.pdb
    wd\test\outputs.json
    wd\outputs.json
    outputs.json

    Can anyone help me on this? 

    Sunday, September 23, 2018 1:21 PM