Look up values using in next lookup

Haider, Mustafa (Reigate) 25

I have got 3 data sets one is Source and other is destination.

In first lookup I have got product family from PostgresSQL as a source of Lookup activity

In Second Lookup I need to pick up IDs against (product family i have selected in first lookup) from SQL Server database as a Lookup activity,

and it will be multiple product families not just first row.

in final copy activity i need productfamily id which i will get from second lookup and map in copyactivity.

How can i do that please?

1 answer

QuantumCache 20,271 Reputation points

2023-10-27T23:55:58.7+00:00
Hello @Haider, Mustafa (Reigate)

To achieve this, you can follow these steps:

Add a Lookup activity to the pipeline and configure it to retrieve the product family from the PostgresSQL database.

Add a ForEach activity to the pipeline and configure it to iterate over the output of the first Lookup activity.

Inside the ForEach activity, add another Lookup activity and configure it to retrieve the IDs against the product family from the SQL Server database.

Add a Copy activity to the pipeline and configure it to use the output of the second Lookup activity as the source and the destination dataset as the destination.

Map the product family ID from the second Lookup activity to the destination dataset in the Copy activity.

Here is an example of how you can configure the Lookup activities:

For the first Lookup activity, set the source dataset to the PostgresSQL database and the query to retrieve the product family. Set the output to an array of strings.

For the second Lookup activity, set the source dataset to the SQL Server database and the query to retrieve the IDs against the product family. Use a parameter to pass the product family from the ForEach activity to the second Lookup activity. Set the output to an array of objects.

Here is an example of how you can configure the ForEach activity:

Set the Items property to the output of the first Lookup activity.

Inside the ForEach activity, add a Set Variable activity to set a variable to the current item in the loop.

Add the second Lookup activity inside the ForEach activity and configure it to use the variable as a parameter.

I hope this helps! Let me know if you have any further questions.
Please sign in to rate this answer.
QuantumCache 20,271 Reputation points

2023-10-31T06:20:20.98+00:00

Hello @Haider, Mustafa (Reigate)

Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!

Haider, Mustafa (Reigate) 25 Reputation points

2023-10-31T09:26:47.23+00:00

Hi Satish, i am away at the moment on Annual leave, will be back on next monday will update and mark as answer after checking.

QuantumCache 20,271 Reputation points

2023-10-31T16:53:53.5833333+00:00

Hello @Haider, Mustafa (Reigate) Thanks for your response.

Haider, Mustafa (Reigate) 25 Reputation points

2023-11-06T12:38:33.08+00:00

Hi Satish,

In step 3, what will be the expression, i need to pass the product family from step 1 lookup select distinct ProductFamilyId from dbo.productFamily where productfamilyname = Lookup value right, what will be the expression?

Haider, Mustafa (Reigate) 25 Reputation points

2023-11-06T12:58:32.23+00:00

i have set the variable to current item as suggested, what will be the expression in this picture the lookup get product family id is getting name from postgres which i am passing. getting this error

The variable 'ProductFamily' of type 'String' cannot be initialized or updated with value of type 'Object'. The variable 'ProductFamily' only supports values of types 'String'.

Haider, Mustafa (Reigate) 25 Reputation points

2023-11-08T13:02:21.0766667+00:00

Hi Satish, Could you please check the above comment?

Haider, Mustafa (Reigate) 25 Reputation points

2023-11-08T14:57:04.4133333+00:00

I have written this expression, as you have suggested i have put the variable set the value and i am getting the correct value in variable but i am getting wrong output by doing this expression in second lookup activity within foreach activity.

getting this error

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid column name 'Life Modelling'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid column name 'Life Modelling'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=207,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=207,State=1,Message=Invalid column name 'Life Modelling'.,},],'

@concat('select distinct ProductFamilyId from dbo.productFamily where name = "',variables('GetProductFamilyName'),'"') This expression is generating wrong query putting \ where name = \"Life Modelling\"" should be name = 'Life Modelling' { "source": { "type": "AzureSqlSource", "sqlReaderQuery": "select distinct ProductFamilyId from dbo.productFamily where name = \"Life Modelling\"", "queryTimeout": "02:00:00", "partitionOption": "None" }, "dataset": { "referenceName": "ProductFamilySM", "type": "DatasetReference", "parameters": {} } }

Haider, Mustafa (Reigate) 25 Reputation points

2023-11-08T17:35:44.93+00:00

This expression is working, now i am getting the full query correctly from the lookup activity with in forEach activity

@concat('select distinct ProductFamilyId from dbo.productFamily where name = ','''',variables('GetProductFamilyName'),'''') { "source": { "type": "AzureSqlSource", "sqlReaderQuery": "select distinct ProductFamilyId from dbo.productFamily where name = 'Life Modelling'", "queryTimeout": "02:00:00", "partitionOption": "None" }, "dataset": { "referenceName": "ProductFamilySM", "type": "DatasetReference", "parameters": {} } }

But this is the query, how i will get the result of this query i.e. ProductFamilyId from here to the following copy activity,

I have tried to use set variable activity after lookup activity with in ForEach and I am getting this error

The expression 'activity('lkp_ProductFamilyIdFromSmart').output.value' cannot be evaluated because property 'value' doesn't exist, available properties are 'effectiveIntegrationRuntime, billingReference, durationInQueue'.

Haider, Mustafa (Reigate) 25 Reputation points

2023-11-14T13:16:02.4933333+00:00

Hi SatishBoddu-MSFT , any update on this please?

QuantumCache 20,271 Reputation points

2023-11-15T06:22:12.72+00:00

Haider, Mustafa (Reigate) Please send an email to azcommunity@microsoft.com with the below details, so that we can work offline on this matter.

Your Email Subject : Attn Satish Boddu

Your Email Body content must include the below:

Thread URL: Link to this thread.

Your Azure Subscription ID: <This is must to be provided to us>
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Look up values using in next lookup

1 answer

Your answer