Bulk Load Multiple Worksheets of an Excel Workbook parallelly into SQL DB using Azure Data Factory V2

Question

Is there a way to Bulk Load worksheets into SQL tables using ADF V2 ?

I was able to load 5 Sheets of data by making 10 Data sets (Excel and SQL Tables) using 2 linked Service (Blob & SQL DB). The performance wasn't good even when all of the data activities were executed in parallel.

If I add more sheets to load, then performance degrades significantly. I assume it's because the same excel workbook is consumed and it takes time to open and read considering it's size (50 MB) and becomes a bottleneck.

SSIS could do it only sequentially using the Foreach loop (depends how dynamically the destination table was set)

There must be a better way to load the data rather than creating N*2 number of Data sets (Worksheet Source & Resp SQL Table).

-Thanks

user_1517736 · Answer

As @Mark has posted - "You can click "edit" in the dataset "sheets" property and parameterize that property. Then you can use a pipeline "foreach" to loop through sheet names"

user_86611 · Answer

You could do this in two steps:

First export the excel spreadsheets to csv files, you could combined these to a single file.
Then use Bulk Insert to load the data into the database

See: https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

Bulk Load Multiple Worksheets of an Excel Workbook parallelly into SQL DB using Azure Data Factory V2

2 Answers