How can I use DTS to load 103 spreadsheets of varying names? The names are
close but vary by date. Inside the spreadsheet I can pretty much count on
the columns staying the same but sometimes they do change. Normally I just
use MS Access import for the import and do some clean up. Then import again
to SQL Server. This time I have way too many spreadsheets.

It seems I would have to make a DTS package for each spreadsheet and this
would be a one time event.

Are the spreadsheets all within a given workbook just on seporate tabs?
are the tab names predictable? IE Tab1_20040501, Tab2_20040502,
Tab3_20040503...

You might get caught with varying columns however, you may want to go
through them all and make sure they are all the same (if possible) or at
least all the columns you need exists in all spreadsheets.

The solution for this is going to be daunting regardless.

If you can get all the spreadsheets to have the proper columns in the proper
positions, you can then loop through a list of filenames (with paths) and
import them one by one using an activeX script to loop through all files and
calling the data pump task.

--
Simon Worth


"Rich" <Rich (AT) discussions (DOT) microsoft.com> wrote

All of the spreadsheets are not exactly the same. That is why for now I have
the extra step of using MSAccess to account for misspellings of fields and
such. If I go through a step of placing all the spreadsheets into a single
book, that would be agood thing but anyway I look at it, I have alot of
manual work to do.

"Simon Worth" wrote:

Yes you do - that is unfortunate.
Let me know if you come up with a solution that saves you time and energy.

--
Simon Worth


"Rich" <Rich (AT) discussions (DOT) microsoft.com> wrote