I need to move an Excel 2003 spreadsheet with 7 worksheets into a SQL Server 2005 Database and would like to know the best way to do this. I have looked at the Import/Export Wizard but see some disadvantages with the multiple worksheets. The contents of the worksheets are all the same columns (there is one tab(worksheet) for each month). I have looked at using an SSIS package to move these as well and again see the multiple worksheets as the sticking point. Ultimately I would like to have this data in two tables, so the app team can query against the historical data by vendor information or store information. Any ideas/solutions would be greatly appreciated
I'm not sure why SSIS wouldn't work with multiple work sheets. Assuming you're able to quantify their locations, you can walk the files and walk the worksheets with loops within SSIS and move the data you need to move. You will probably need to ensure that the structures of the worksheets are consistent in order to make the imports work well, especially automating them. Other than that, it ought to be pretty smooth. I'm sure if you searched on the web there are even examples available.