I have an issue where we are on office 2007 and there is a row limit in excel, so the business users are putting data exports in 3 different sheets. The sheets are formatted almost the same, where sheet1 will have the header data, and sheets2, 3, etc will only have the data. Can anybody help me figure out the best way to use BIDS 2008 to dynamically get the number of worksheets in a workbook, then union all the results together?
I was thinking something like this:
IF EXISTS (SELECT something from workbook to get sheets)
SELECT * FROM [$SHEET1]
SELECT * FROM [SHEET2]
SELECT * FROM [SHEET3]