Looking for tutorial on how to read Excel file and for each row look up tracknum in multiple datasources?

Question

I use Azure Data Factory on their web-based portal, and after doing a tutorial I'm still a beginner. I would like to read an Excel file. One column in the Excel file is "tracknum". I will need to look up the tracknum in a specific column in datasource1. If it does not exist in datasource1 I will have to look it up in a specific column in datasource2. I may need to use an SQL statement for each look up. If the tracknum is found I will need to return multiple fields from the datasource.

I have searched the internet for this and have not found anything close to this. And the tutorial I did months ago did not seem to cover this. It only covered super simple examples.

The AI here gave me an answer but it was not detailed enough.

Thank you.

EDIT: Please be patient as the US has a 4 day holiday weekend and many people are on vacation until Monday.

Answer 1

@Chuck Roberts

Thanks for using Microsoft Q&A forum and posting your query.

Here’s a step-by-step guide to help you read an Excel file, look up the “tracknum” in multiple data sources, and return the required fields:

Step-by-Step Guide

Create Linked Services:

• Set up linked services for your Excel file and the data sources (datasource1 and datasource2). This allows ADF to connect to these data stores.

Create Datasets:

• Create datasets for the Excel file and the tables in datasource1 and datasource2. Ensure the Excel dataset is configured to read the “tracknum” column.

Create a Pipeline:

• In your pipeline, add a Lookup Activity to read the Excel file. Configure it to retrieve the “tracknum” values.

ForEach Activity:

• Add a ForEach Activity to iterate over each “tracknum” retrieved from the Excel file. Inside the ForEach activity, add the following steps:

Lookup in datasource1:

• Add a Lookup Activity inside the ForEach to check if the “tracknum” exists in datasource1. Use an SQL query to search for the “tracknum”.

If Condition Activity:

• Add an If Condition Activity to check the output of the Lookup in datasource1. If the “tracknum” is found, proceed to the next step. If not, perform a lookup in datasource2.

Lookup in datasource2:

• If the “tracknum” is not found in datasource1, add another Lookup Activity to search for the “tracknum” in datasource2.

Return Fields:

• Depending on where the “tracknum” is found, use a Copy Activity or another appropriate activity to return the required fields from the respective data source.

Helpful Resources

• Excel file format in Azure Data Factory
• Lookup Activity in Azure Data Factory
• ForEach Activity in Azure Data Factory

Hope this helps. Do let us know if you any further queries.