Tab per table

Question

Hello Team,

I have one csv file. Under that there are 5 tabs. So I need to transfer the data from each tab to each different tables in the Target Table

ex :

Tab1: TableA
Tab2: TableB
Tab3: TableC
Tab4: TableD
Tab5: TableE

How this is possible in ADF.Please advise me and if you have any supporting link. Please provide me.

Regards
RK

Answer 1

Hello @Rohit Kulkarni .

The Copy activity only does 1 excel tab/page at a time. To re-use the same dataset for multiple pages, we need to parameterize the dataset. This is good news because on the sink side, the copy activity only does one table at a time anyway.
So, the solution, is to make a (ForEach) loop over the associations of tabNumber : tableName. We will pass the tabNumber to the source Dataset, and the tableName to the sink Dataset.

Below is how I parameterize the source excel Dataset.

Answer 2

@Rohit Kulkarni

The correlation of tab/page to table can be done either in a parameter, or a lookup activity pointed at a file like your example. In both cases we want to iterate over the correlation.

In below example, I encode the correlation in a parameter called "correlation" of type array

[{"page":1,"table":"foo"},{"page":2,"table":"bar"}]  

In the ForEach Items I use

@pipeline().parameters.correlation  

In the Copy activity source, I reference the tab/page

@item().page  

In the Copy activity sink I reference the table name

@item().table