none
Lookup activity- selecting elements from value array

    Question

  • Hi Team

    I have a lookup activity which has the value array as the output.PFB ,

    { "count": 6, "value": [ { "SourceDirectory": "I01", "SourceFileName": "debug_ns", "SourceFile": "debug_ns.log20180825" }, { "SourceDirectory": "I01", "SourceFileName": "sentFailed_ns", "SourceFile": "sentFailed_ns.log20180825" }, { "SourceDirectory": "I01", "SourceFileName": "heartbeat_ns", "SourceFile": "heartbeat_ns.log20180825" }, { "SourceDirectory": "I02", "SourceFileName": "heartbeat_ns", "SourceFile": "heartbeat_ns.log20180825" }, { "SourceDirectory": "I02", "SourceFileName": "debug_ns", "SourceFile": "debug_ns.log20180825" }, { "SourceDirectory": "I02", "SourceFileName": "sentFailed_ns", "SourceFile": "sentFailed_ns.log20180825" }, ], "effectiveIntegrationRuntime": "DOMEBIintegrationRuntimeName01" }

    In the next activity,metadata activity,I need to select the corresponding Sourcedirectory in run time.How to get the source Directory of all the zero indexes in the value array of lookup activity to metadata activity.


    shankar

    Tuesday, August 28, 2018 9:20 AM

Answers

  • Hi Shankar,

    Three quick questions:

    1. what's the source dataset of your copy activity?

    (I guess you were trying to simply insert "Source directory" in array "value" into SQL SERVER, if so, you'll need Stored Procedure activity, not copy activity)

    2. which value you wanna insert into "Source directory"? each "Source directory" in array "value" or a certain "Source directory" with the specific index?

    3. let's assume you'll use Stored Procedure activity (not Copy activity), does the Stored Procedure activity running directly after lookup activity(not the foreach activity) meets your requirement? If so, we can easily access the output of the lookup activity.

    Thanks.


    Tuesday, August 28, 2018 1:28 PM

All replies

  • Hi Shankar,

    To access all "SourceDirectory" in zero-based array, you need to involve a For Each activity to wrap the Get Metadata activity.

    Pass the "@activity('MyLookupActivity').output.value" to the items property in For Each activity, and then use "@item().SourceDirectory" in Get Metadata activity to access every "SourceDirectory" in value array.

    Thanks.


    Tuesday, August 28, 2018 10:49 AM
  • Hi Wang

    Thanks for your quick response.

    As you said i have a foreach activity and i have given @activity('MyLookupActivity').output.value in the items property of foreach. Now outside of foreach i need to have a copy activity to SQL server which will run only once outside of foreach.

    Now i need to insert a column in sql server "Source directory" .How to get the value for this column.

    can you please tell the expression for getting the Source directory

    The pipeline will look like the below:

    Lookup activity -> foreach activity -> copy activity


    shankar

    Tuesday, August 28, 2018 11:16 AM
  • Hi Shankar,

    Three quick questions:

    1. what's the source dataset of your copy activity?

    (I guess you were trying to simply insert "Source directory" in array "value" into SQL SERVER, if so, you'll need Stored Procedure activity, not copy activity)

    2. which value you wanna insert into "Source directory"? each "Source directory" in array "value" or a certain "Source directory" with the specific index?

    3. let's assume you'll use Stored Procedure activity (not Copy activity), does the Stored Procedure activity running directly after lookup activity(not the foreach activity) meets your requirement? If so, we can easily access the output of the lookup activity.

    Thanks.


    Tuesday, August 28, 2018 1:28 PM
  • Thanks for your response wang.

    I will check and let you know if any help is required.

    Appreciate your quick response.

    Regards

    Shankar


    shankar

    Wednesday, August 29, 2018 4:14 AM