Recently one of our development teams has increased the request of importing an Excel file with 20 sheets in to 20 tables in a database from about once a quarter to multiple times a week and this past Monday was three times in one day. I have been the lucky DBA to get these requests as of late and after Monday I was determined to fix the process. The current procedure is to use the good ol’ Import/Export Wizard since this was a rare request. (This included a lot of point and click and possibility for manual error) With increased requests and increased table counts I knew there had to be a better way to get this accomplished without grimacing each time I see the request.
Garry has a script which he uses, but which can be tailored for other uses pretty easily.