Create New Excel Sheet if current sheet is full(65536) using SSIS

Question

Post reply

Create New Excel Sheet if current sheet is full(65536) using SSIS

rohit.kumar.barik

Say Hey Kid

Points: 672
More actions
August 2, 2010 at 3:45 am

#227364

Hi All
I have created an excel sheet using create table.[In SSIS Execute SQL task]
I am inserting records into the excel sheet from one table.
It is working file and i can see the values.
But i am getting error when the records is exceeds 65535 i.e. record size per excel sheet.
So i need to create another sheet that will contain rest records dynamically.
[i.e. if record is more than 65536 than it will create another sheet and store all the records.
Please help me to do it.
thanks in Advance.
Rohit

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

You might consider using the export to Excel 2007

Raunak J

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

Even better...make table partitions of your dataset.

then send each partition to a respective sheet

Raunak J

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Raunak Jhawar (8/2/2010)
Even better...make table partitions of your dataset.
then send each partition to a respective sheet

I still think your first answer was better (Excel 2007). Even better than that, however, is to opt for .CSV as your output format.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rohit.kumar.barik Say Hey Kid Points: 672 More actions · Answer 4

Thanks For your suggestion.

I am using SSIS 2005. and it is not possible to export records to Excel 2007.

[Since it is supporting only .xls files.]

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

Rohit I have another solution...considering the restrictions posed

You may use variables TO and FROM variables to filter your query inside a foreach loop

(Assuming you have implemented ROW_NUMBER() to get distinct row number's)

For instance you have 100000 records

Thus your loop will run 2 times (Loop_Count=No. of records\Excel number of rows)

In side foreach

you will have an EST(To create new excel sheet) followed by DFT ( To Perform export)

and Script Component to modify your variables

Thus for first run...TO=1 FROM=65536

second run TO=65537 FROM=100000

Raunak J

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 6

Link below will help you create multiple sheets.. I have used the same method Raunak Suggested in the past and i think its a good work around if cannot use XL 2007 or Csv

http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.html]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

rohit.kumar.barik

May I suggest that you read this article, which contains a wealth of sample code ... It has code to name Excel spread sheets, add work sheets, write to a selected sheet, etc., etc.

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Surely, when you start getting to the realms of datasets with more than 65k rows, a spreadsheet is not the right repository. You need a database solution (Access maybe) or a flat-file solution.

Unless you're telling me that the recipient of a multi-sheet Excel file containing several-hundred-thousand rows is actually going to build multi-sheet analytics on it, or is an accountant (!), this requirement should be reviewed asap, in my opinion.

Go ahead - prove me wrong 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 9

Raunak Jhawar

SSCoach

Points: 15701

August 3, 2010 at 10:23 pm

#1201876

Yes Phil you are correct

Raunak J

Bonediggler SSC Veteran Points: 204 More actions · Answer 10

Raunak--

Could you please expand on your solution (provide details) using the TO/FROM variables? I would like to implement, but am somewhat new to SSIS and need a bit of hand holding.

Thank you!!