CODEWITHSUNDEEP
azure-data-factory
Kenny_I
Kenny_I

Reputation: 2503

How to use result of Lookup Activity in next Lookup of Azure Data Factory?

I have Lookup "Fetch Customers" with SQL statement:

  Select Count(CustomerId) As 'Row_count' ,Min(sales_amount) as 'Min_Sales' From [sales]. 
  [Customers]

It returns value

   10, 5000

Next I have Lookup "Update Min Sales" with SQL statement, but getting error:

    Update Sales_Min_Sales
    SET Row_Count = @activity('Fetch Customers').output.Row_count,
    Min_Sales = @activity('Fetch Customers').output.Min_Sales
    Select 1

Same error occurs even I set Lookup to

   Select @activity('Fetch Fetch Customers').output.Row_count

Error:

 A database operation failed with the following error: 'Must declare the scalar variable 
"@activity".',Source=,''Type=System.Data.SqlClient.SqlException,Message=Must declare the 
scalar variable "@activity".,Source=.Net SqlClient Data 
Provider,SqlErrorNumber=137,Class=15,ErrorCode=-2146232060,State=2,Errors= 
[{Class=15,Number=137,State=2,Message=Must declare the scalar variable "@activity".,},],'

Upvotes: 1

Views: 2075

Answers (1)

All About BI
All About BI

Reputation: 533

I have similar set up as yours. Two lookup activities.

enter image description here

First look up brings min ID and Max ID as shown

{
    "count": 1,
    "value": [
        {
            "Min": 1,
            "Max": 30118
        }
    ],
    "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (East US)",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.016666666666666666,
                "unit": "DIUHours"
            }
        ]
    },
    "durationInQueue": {
        "integrationRuntimeQueue": 22
    }
}

in my second lookup i am using the below expression

Update saleslt.customer set somecol=someval where CustomerID=@{activity('Lookup1').output.Value[0]['Min']}

Select 1 as dummy

Just that we have to access lookup output using indices as mentioned and place the activity output inside {}.

Upvotes: 2

Related Questions