Import Multiple Excel Spreadsheets into an SQL table

[budgetboy]

Dear helpers,

I have an SQL table called DEPOT STOCK and I want to set up a DTS which can import multiple spreadsheets into this one table. I dont want to set up seperate connections and data tasks for each spreadsheet. The spreadsheets are in a folder called Result in \\Vpszzz\data\Apps\Chas Data\Depot Stock Valuation\France. The SQL table and excel spreadsheets will have the same column headings. I want it so that when I add new spreadsheets into the folder it will be very easy to change the dts/routine to bring the new spreadsheet(s) in.

I believe I will probably have to use some sort of data pump and global variables. I would be very grateful if someone had the code for this whole process or knew a very suitable site.

The spreadsheets are called:

Marseille.xls
Nice.xls
Northwest.xls

Thanks

 

[rodeomount]

Is there some reason why you wouldn't want more than one connection in your DTS package? You can run the import of each .xls file in parellel.