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!
LVL 1
sqlagent007Asked:
Who is Participating?
 
LIONKINGCommented:
Maybe this post can help.

http://www.mssqltips.com/sqlservertip/1674/retrieve-excel-schema-using-sql-integration-services-ssis/

Or... You can use variables and a Script Task.

Inside this task you can code the retrieval of the sheet names, as done in this example.
Then you can assign the sheet names to the variables you have declared.

Hope it helps.
0
 
DcpKingCommented:
The row limit in 2007 is somewhere about 1,000,000 - it was 65,400 odd in 2003 and before.

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!
0
 
sqlagent007Author Commented:
Exactly what I needed! THANK YOU!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.