[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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!
0
sqlagent007
Asked:
sqlagent007
1 Solution
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now