How to define schema when copying Excel file with several sheet with different schema?

Question

I am going to use a Copy activity in ADF to copy excel files from ADLS to Synapse Data Warehouse.

Excel file has several sheets with different column names and schema. A list of excel sheets are passed to the ADF pipeline as a parameter to be processed.

How can I define different Schemas in Dataset object in ADF in this scenario?

Background:

When I trigger Copy activity without defining or mapping Schema, I encounter the below error:

ErrorCode=DelimitedTextColumnNameNotAllowNull,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The name of column index 1 is empty. Make sure column name is properly specified in the header row.,Source=Microsoft.DataTransfer.Common

I searched about this error and checked the source file:

• I have checked all columns in the excel file. All columns have names. There is no empty cell in the 1st row of the excel file. There is no comma, space, or special character in column names.

• Dataset used as Source in the Copy activity is Excel (not CSV).

• "First row as header" option is enabled.

• When I use "Preview data" option, I can get a sample of data in ADF, which looks fine. All column names are similar to the source file.

• Dataset object allows us to define Schema. When I import schema for every excel sheet, it looks fine. But, the problem is there are several sheets in the excel with different schema.

As a potential solution, I am going to define schema for the excel sheets given that every excel sheet has a different schema.