Dynamically named EXCEL file needs to be imported into SQL.
Thank you all in advance!
We would like to create a table in SQL from an Excel.
I've configured a WMI Event Watcher Task in SSIS that can start a data Flow when any file is dropped into the watch folder, however because the name of the excel files changes, I'm having trouble configuring the "Excel Source" component. Also, on occasion, the names of the columns may change in the excel file.
If we could import the dynamically named excel into sql with a static name as a table, we could probably then set up some stored procs to work the data over.
Just not sure how to get the excel file into SQL.
thank you all in advance!
We would like to create a table in SQL from an Excel.
I've configured a WMI Event Watcher Task in SSIS that can start a data Flow when any file is dropped into the watch folder, however because the name of the excel files changes, I'm having trouble configuring the "Excel Source" component. Also, on occasion, the names of the columns may change in the excel file.
If we could import the dynamically named excel into sql with a static name as a table, we could probably then set up some stored procs to work the data over.
Just not sure how to get the excel file into SQL.
thank you all in advance!
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
What's your SQL Server version?
You can solve this easily with FileTables. You will have a table filled with new records everytime a new file is dropped in the folder and you will not need anything for that.
You can create a trigger on the table and start the SSIS package, which can process one specific file (since it was started by the trigger) or can use a foreach over the records.