Do I need multiple datasets in ADF when loading multiple sheets from one xls?

geert vanhove 121

I have 1 xls file with multiple tabs (sheets).
When using 'copy data' in a pipeline, I need to define the Sheet Name in the dataset.
Question: Do I need multiple datasets in ADF when loading multiple sheets from one xls?

Vaibhav Chaudhari 38,736 Reputation points

2020-11-06T11:36:36.207+00:00

What is the target (sink) ? Do you want to load data from 3 sheets to 3 SQL tables (using 3 copy activities) or single copy activity - load 3 sheets data to single SQL table?
geert vanhove 121 Reputation points

2020-11-06T11:47:12.19+00:00

I need to load 3 sheets data into single SQL table but filling partially different columns.
Eg sheet1 contains col1, col2 and col3; sheet2 contains col1, col4 and col5? Col1 is shared between different sheets, the other are individual.

Accepted answer

geert vanhove 121 Reputation points

2020-11-09T11:16:08.417+00:00
I found the solution myself:

Open the dataset and add a parameter (eg SheetName)

In the connection tab, use dynamic content for sheet name. Choose parameter created in previous step. (eg @dataset().SheetName)

In the copy task, open the source tab and it will ask to fill out the parameter called SheetName.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

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

Share via

Do I need multiple datasets in ADF when loading multiple sheets from one xls?

0 additional answers

Your answer