ADF: DataFlow: Lookup: Match Multiple Rows Error: First and last match lookup requires column ordering of the looked up table

Question

Hi,

I'm getting the below error when trying to return multiple records ('Match multiple rows') from a Lookup table (in data flow):

"shortMessage":"DF-LKP-001 at : First and last match lookup requires column ordering of the looked up table\

I understand what it wants me to do but having trouble finding where I can order my lookup stream. I tried sorting the rows from the source and then using that stream but it did not work. Any help on this would be greatly appreciated.

Thanks.

Answer 1

Hi @PageChristopher-5206

Thanks for the question and using MS Q&A platform.

As I understand, you are working on a data flow within a data integration pipeline. This data flow involves a lookup operation, where you are trying to retrieve multiple matching records from a lookup table. You are running into an error when trying to return multiple records from a Lookup table in a data flow. The error message is indicating that the looked-up table needs to have column ordering specified.

To resolve this, you'll need to ensure that the Lookup table has a defined ordering, such as a primary key or a sorted column. This is because the Match multiple rows option requires a way to determine which row to return when there are multiple matches.

It looks like you are on the right track by trying to sort your lookup stream. However, the specific steps to achieve this can vary depending on the tool you are using for your data flow. Here, I will provide a general approach and then break it down for some common tools like Azure Data Factory and SSIS.

1. Azure Data Factory: If you are using Azure Data Factory, you can follow these steps:

Add a Sort Transformation: In your data flow, add a Sort transformation before the Lookup transformation.

Configure the Sort Transformation: Click on the Sort transformation to configure it. In the Sorting Criteria section, add the key column(s) that you are using for the lookup. Specify the sort order (ascending or descending) as required.

Connect the Sorted Stream to the Lookup: Connect the output of the Sort transformation to the Lookup transformation. Configure your Lookup transformation as needed.

2. SSIS (SQL Server Integration Services): For SSIS, the steps are slightly different:

Add a Sort Component: In the Data Flow tab, drag a Sort component onto the canvas.

Configure the Sort Component: Double-click the Sort component to open the configuration window. In the Input Columns tab, select the key column(s) that you are using for the lookup. Specify the sort order (ascending or descending) for each key column.

Connect the Sorted Stream to the Lookup: Connect the output of the Sort component to the input of the Lookup transformation. Configure your Lookup transformation to use the sorted data.

By following these steps, you should be able to sort your lookup stream correctly and resolve the issue with the DF-LKP-001 error.

I hope this information helps.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.