Client has a half dozen Excel files (same format) that they want to upload to SQL Server every month from their accounting software, so they can use some BI tool to analyze the data.
Over 1.5 million rows of data each month that must be massaged before getting it into SQL Server (some of the date fields are just month and year, and some of the numeric fields contains spaces).
I've been doing this via Access, but the entire process, loop through folder, identify each table, link that table to Access, and append records to SQL Server is taking close to 12 hours (with SQL Server transaction log disabled).
I've not used SQL Server 2008 too extensively, so I'm trying to determine whether there is a better (less time consuming) way to accomplish this. Does SQL Server 2008 still have a DTS function which would allow me to go directly from Excel to SQL Server? If so, would I use a stored procedure for that? How would I set that up to process files with different names and in different folders each month?