dynamic excel sheet name

Question

hi,

i have a excel file where sheet name is changing every time.

how to load file dynamically. i know i can use script task, but can anyone provide me code for vb.net.

thanks

Answer 1

Hi @coool sweet ,

No need for the vb.net

It is possible to use Microsoft ACE OLEDB provider to retrieve data from MS Excel file.
Regular SSIS OLEDB Source in DFT.
You can construct the T-SQL statement dynamically by modifying two parts:

• Database
• Sheet

SQL

SELECT *  
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
   'Excel 12.0 Xml; HDR=NO; IMEX=1;  
    Database=c:\Users\ykhab\OneDrive\Documents\dada.xlsx',  
    [Sheet1$]);  

Answer 2

Hi @coool sweet ,

To use Script Task to get the sheet name of the excel you may also refer below which provided the code.

get-the-sheetname-of-the-excel-sheet-using-script-task

how-to-load-excel-file-name-and-sheet.html

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.