Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create New Excel Sheet if current sheet is full(65536) using SSIS Expand / Collapse
Author
Message
Posted Monday, August 2, 2010 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:23 AM
Points: 16, Visits: 46
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
Post #962051
Posted Monday, August 2, 2010 4:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
You might consider using the export to Excel 2007

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #962057
Posted Monday, August 2, 2010 5:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
Even better...make table partitions of your dataset.
then send each partition to a respective sheet



Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #962106
Posted Monday, August 2, 2010 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 5,189, Visits: 12,053
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #962147
Posted Tuesday, August 3, 2010 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:23 AM
Points: 16, Visits: 46
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.]
Post #962774
Posted Tuesday, August 3, 2010 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
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



Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #962790
Posted Tuesday, August 3, 2010 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:51 PM
Points: 215, Visits: 556
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]
Post #962799
Posted Tuesday, August 3, 2010 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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

Before posting a performance problem please read
Post #962815
Posted Tuesday, August 3, 2010 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 5,189, Visits: 12,053
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #962832
Posted Tuesday, August 3, 2010 10:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
Yes Phil you are correct

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #963222
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse