How to dynamically determine the amount of worksheet in a workbook for SSIS data load
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]
UNION
SELECT * FROM [SHEET2]
UNION
SELECT * FROM [SHEET3]
.....
Thanks!
I was thinking something like this:
IF EXISTS (SELECT something from workbook to get sheets)
SELECT * FROM [$SHEET1]
UNION
SELECT * FROM [SHEET2]
UNION
SELECT * FROM [SHEET3]
.....
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly what I needed! THANK YOU!
Anyway, where are they getting their data from? I'd bet that they're getting them from some mainframe system that throws out a huge .csv file that they then laboriously chop into pieces to see in Excel, and then you'll have to labour at importing, when you could easily just grab the .csv, import it, and then export it to Excel for them, making their lives easier and your workload go away!