Read data from all sheets of an excel file using OPENROWSET
I'am currently working on an SSIS project and I want to get the data from all sheets of
an Excell file (.xlsx) that I need to import it into an SQL table periodically. I don't know each time the number of sheets its contains.
I know how to deal with one sheet using for example
Select * from Openrowset (MicrosoftFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;IMEX=1; Database=E:\export\file.xlsx',
'SELECT * FROM [sheet1$]'
Is there a clever way to get all the sheets? I suspect that somehow i must get the excelschema via script to get the sheet name in an variable and then to use a For-Loop . If that so then could any one can help me with the script..
Thanks in Advance